How to Find Compressed Objects and Their Properties in SQL Server

📦 How to Find Compressed Objects and Their Properties in SQL Server

SQL Server offers data compression to help reduce storage requirements and improve I/O performance. Over time, it's important to review which objects in your database are compressed, what type of compression is used, and whether further optimization or decompression might be needed.

The following query helps you identify compressed objects in your database along with key details, such as the compression type and a ready-to-run command to remove compression if needed.

🧾 SQL Query to List Compressed Database Objects

SELECT 
    SCHEMA_NAME(o.schema_id) AS [Schema Name],
    OBJECT_NAME(o.object_id) AS [Object Name],
    'ALTER INDEX ALL ON ' + SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) + 
    ' REBUILD WITH (DATA_COMPRESSION = None);' AS [Decompress CMD],
    (
        SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME(o.object_id)), 'TableHasVarDecimalStorageFormat')
    ) AS [Table Has VarDecimal Storage Format],
    p.[rows],
    p.[data_compression_desc] AS [Compression Type],
    p.[index_id] AS [Index ID on Table]
FROM 
    sys.partitions p
INNER JOIN 
    sys.objects o ON p.object_id = o.object_id
WHERE 
    p.data_compression > 0
    AND SCHEMA_NAME(o.schema_id) <> 'sys'
ORDER BY 
    [Schema Name],
    [Object Name];

🧠 What This Query Does

• Retrieves a list of all compressed objects (tables or indexes) in the current database.
• Shows the schema and object names for clarity.
• Displays the compression type (ROW, PAGE, or COLUMNSTORE).
• Provides an ALTER INDEX command to decompress each object if needed.
• Checks whether a table uses the VarDecimal Storage Format, which helps save space for decimal values.

✅ Why This Is Useful

• Helps DBAs audit storage savings across the database.
• Assists in evaluating whether certain tables or indexes should be recompressed, left as-is, or decompressed.
• Supports performance tuning efforts when analyzing I/O or CPU trade-offs for compression.

---

💡 Tip: Regularly reviewing compressed objects is a good habit—especially in environments where performance and storage costs are closely monitored.