Today I am going to discuss about Data Compression available in SQL Server 2008 very briefly. SQL 2008 supports two ROW level and PAGE level compression for tables and indexes. Compression is supported for the following database objects:

1) Heap Table
2) Non Clustered Table
3) Clustered Table
4) Indexed View
5) Partitioned Tables and Indexes

Compression is a big topic, today I am not going to discuss about it in detail. I am saving it for another post in future. Today I am going to show a simple query through which you can determine the Compression for different objects in your database.

SELECT
 OBJECT_NAME(OBJECT_ID) AS [TABLE NAME], 
CASE WHEN DATA_COMPRESSION=0 THEN ‘No Compression’
            WHEN DATA_COMPRESSION=1 THEN ‘Row Level Compression’
            WHEN DATA_COMPRESSION=2 THEN ‘Page Level Compression’
 END ‘Compression Level’,
 DATA_COMPRESSION AS [DATA COMPRESSION] FROM sys.partitions 

Cheers,

Subhro Saha

 


Advertisements