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

Dealing with columns storing compressed data requires special handling on the application side to ensure correct data access and manipulation in T-SQL.

Description

Failing to handle compression and decompression of compressed columns can result in unexpected behaviors, including incorrect query results and data integrity issues.

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

The CompressedColumnsList parameter specifies a list of columns that require the use of compression functions. 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

Example of problematic query:

SQL
Copy
1SELECT CompressedColumn FROM DemoTable WHERE CompressedColumn = 'SomeValue';

In this query, accessing the CompressedColumn without decompressing it could produce incorrect results if the predicate doesn’t properly account for compression. This highlights the importance of using appropriate compression functions like DECOMPRESS and COMPRESS.

When accessing compressed columns, these functions must be explicitly invoked to read or write data correctly. SQL Server does not inherently mark columns as compressed, so developers need to manage this knowledge explicitly, either through documentation or schema conventions.

Explicit handling of compressed column data is essential, as failing to do so can result in runtime errors, incorrect application logic, and inefficiencies. Always ensure the use of DECOMPRESS when reading compressed data and COMPRESS when storing data back into compressed columns.

  • Incorrect or inefficient query results due to not decompressing data before use.

  • Potential data integrity issues when compressed data is updated without reapplying necessary compression functions.

Ensure that all T-SQL operations involving compressed columns incorporate appropriate compression and decompression logic to avoid runtime errors or unexpected behaviors.

How to fix

This rule addresses issues related to handling columns with compressed data in SQL Server to prevent inefficient queries or data integrity issues.

Follow these steps to address the issue:

  1. When updating or inserting data into a compressed column, use the COMPRESS() function to ensure data is stored correctly. For example, wrap your value insertion like this: COMPRESS(‘YourValue’).

  2. When using data from a compressed column in a query, ensure it is decompressed using the DECOMPRESS() function. This will allow correct evaluation and manipulation of the data.

  3. Review existing queries to ensure they are not directly accessing a compressed column without proper compression handling.

Example of corrected query:

SQL
Copy
1SELECT DECOMPRESS(CompressedColumn) FROM DemoTable WHERE DECOMPRESS(CompressedColumn) = 'SomeValue';

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 requires Analysis Context. If context is missing, the rule will be skipped during analysis.

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  column_name
 2FROM  schema_name.table_name
 3
 4SELECT  DECOMPRESS( 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 ( id , column_name )
11SELECT  1 , 'abc'  
12
13INSERT INTO schema_name.table_name(  id , column_name) 
14VALUES  (  'name', COMPRESS( 'abc'))
Example Test SQL with Automatic Fix
SQL
Copy
 1SELECT  DECOMPRESS(column_name)
 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 ( id , column_name )
11SELECT  1 , COMPRESS('abc')  
12
13INSERT INTO schema_name.table_name(  id , column_name) 
14VALUES  (  'name', 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. 1 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. 10 42
See Also

Other Resources

Analysis Rules