Click or drag to resize

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 are 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

Scope

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

Parameters
NameDescriptionDefault Value
CompressedColumnsList

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

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.

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Categories

Explicit Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
 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')

Analysis Results

No violations found.

See Also

Other Resources