EX0025 : Compressed column used without decompression or updated/inserted without compression

The topic describes the EX0025 analysis rule.

Message

Compressed column used without decompression or updated/inserted without compression

Description

The rule checks T-SQL code for a configured list of columns, which store a compressed data and require compression and decompression when updated or used in an expression.

The comma separated list of columns, which require usage of compression functions, can be provided using the CompressedColumnsList parameter and the following column name formats are supported:

– database_name.schema_name.table_name.column_name

– schema_name.table_name.column_name

– table_name.column_name

How to fix

When the reported compressed column is updated or inserted, wrap the column value in a COMPRESS() function call. When the column is used in an expression, it have to be wrapped in a DECOMPRESS() function call.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
CompressedColumnsList

A comma separated list of fully qualified column names. The names can be either database or schema qualified.

database_name.schema_name.table_name.column_name,schema_name.table_name.column_name,table_name.column_name
NotDecompressedMessage

The parameter specifies the rule violation message when a compressed column, which is not decompressed when used is found.

its value has to be decompressed using DECOMPRESS() function when used in an expression.
NotCompressedMessage

The parameter specifies the rule violation message when a compressed column is inserted or updated without having the new value compressed.

its value has to be compressed using COMPRESS() function when column is updated or inserted.
CanWorkWithoutContext

Parameters specifies whether the rule can work without Analysis Context.

yes
CanWorkWithoutSqlConnection

Parameters specifies whether the rule can work without SQL Connection.

yes
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
5 minutes per issue.
Categories

Explicit Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT  *
 2FROM  schema_name.table_name
 3
 4SELECT  column_name
 5FROM  schema_name.table_name
 6
 7UPDATE a SET  column_name='abc'
 8FROM  schema_name.table_name AS a
 9
10INSERT INTO schema_name.table_name(  column_name
11                                   , column2
12                                   , column3) VALUES  (  'abc'
13                                                       , COMPRESS( 'abc')
14                                                       , 3)
15                                                    , (  1
16                                                       , 2
17                                                       , 3)
18
19INSERT INTO schema_name.table_name(  column_name
20                                   , column2
21                                   , column3)
22SELECT 'abc' , 2
23
24INSERT INTO schema_name.table_name(  id
25                                   , column_name)
26SELECT 1 , 'abc' AS c
27
28INSERT INTO schema_name.table_name(  id
29                                   , column_name)
30SELECT 1 , c='abc'
31
32INSERT INTO schema_name.table_name(  id
33                                   , column_name
34                                   , column3)
35SELECT 1 , 'c'='abc' , 3
36
37SELECT  DECOMPRESS( column_name)
38FROM  schema_name.table_name
39
40UPDATE a SET  column_name=COMPRESS( 'abc')
41FROM  schema_name.table_name AS a
42
43INSERT INTO schema_name.table_name(  id
44                                   , column_name) VALUES  (  'name'
45                                                           , COMPRESS( 'abc'))
46
47INSERT INTO schema_name.table_name(  id
48                                   , column_name)
49SELECT 1 , COMPRESS( 'abc') AS c
50
51INSERT INTO schema_name.table_name(  id
52                                   , column_name)
53SELECT 1 , c=COMPRESS( 'abc')
54
55INSERT INTO schema_name.table_name(  id
56                                   , column_name)
57SELECT 1 , 'c'=COMPRESS( 'abc')
58
59INSERT INTO schema_name.table_name(  id
60                                   , column_name)
61SELECT 1 , COMPRESS( 'abc')
Example Test SQL with Automatic Fix
SQL
Copy
 1SELECT  *
 2FROM  schema_name.table_name
 3
 4SELECT  DECOMPRESS(column_name)
 5FROM  schema_name.table_name
 6
 7UPDATE a SET  column_name=COMPRESS('abc')
 8FROM  schema_name.table_name AS a
 9
10INSERT INTO schema_name.table_name(  column_name
11                                   , column2
12                                   , column3) VALUES  (  COMPRESS('abc')
13                                                       , COMPRESS( 'abc')
14                                                       , 3)
15                                                    , (  COMPRESS(1)
16                                                       , 2
17                                                       , 3)
18
19INSERT INTO schema_name.table_name(  column_name
20                                   , column2
21                                   , column3)
22SELECT COMPRESS('abc') , 2
23
24INSERT INTO schema_name.table_name(  id
25                                   , column_name)
26SELECT 1 , COMPRESS('abc') AS c
27
28INSERT INTO schema_name.table_name(  id
29                                   , column_name)
30SELECT 1 , c=COMPRESS('abc')
31
32INSERT INTO schema_name.table_name(  id
33                                   , column_name
34                                   , column3)
35SELECT 1 , 'c'=COMPRESS('abc') , 3
36
37SELECT  DECOMPRESS( column_name)
38FROM  schema_name.table_name
39
40UPDATE a SET  column_name=COMPRESS( 'abc')
41FROM  schema_name.table_name AS a
42
43INSERT INTO schema_name.table_name(  id
44                                   , column_name) VALUES  (  'name'
45                                                           , COMPRESS( 'abc'))
46
47INSERT INTO schema_name.table_name(  id
48                                   , column_name)
49SELECT 1 , COMPRESS( 'abc') AS c
50
51INSERT INTO schema_name.table_name(  id
52                                   , column_name)
53SELECT 1 , c=COMPRESS( 'abc')
54
55INSERT INTO schema_name.table_name(  id
56                                   , column_name)
57SELECT 1 , 'c'=COMPRESS( 'abc')
58
59INSERT INTO schema_name.table_name(  id
60                                   , column_name)
61SELECT 1 , COMPRESS( 'abc')
Analysis Results
  Message Line Column
1 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be decompressed using DECOMPRESS() function when used in an expression. 4 8
2 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 7 14
3 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 12 57
4 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 15 57
5 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 19 37
6 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 25 37
7 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 29 37
8 EX0025 : Column [schema_name].[table_name].[column_name] is a stored as compressed and its value has to be compressed using COMPRESS() function when column is updated or inserted. 33 37
See Also

Other Resources

Analysis Rules