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.

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:
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.

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:
-
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’).
-
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.
-
Review existing queries to ensure they are not directly accessing a compressed column without proper compression handling.
Example of corrected query:
1SELECT DECOMPRESS(CompressedColumn) FROM DemoTable WHERE DECOMPRESS(CompressedColumn) = 'SomeValue';

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

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 |

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.


Explicit Rules, Bugs

There is no additional info for this rule.

SQL
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')) |

SQL
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')) |

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 |
