Microsoft SQL Server 2012 Bible

Applying Data Compression

Although data compression is complicated, actually enabling data compression is a straightforward task using either the Data Compression Wizard or an ALTER command.

Determining the Current Compression Setting

When working with compression, the first task is to confirm the current compression setting. Using the Management Studio UI, you can view the compression type for any single object in two ways:

• The Table Properties or Index Properties Storage page displays the compression settings as a read-only value.
• The Data Compression Wizard, found in Object Explorer (Context menu → Storage → Manage Compression) opens with the current compression selected.

To see the current compression setting for every object in the database, run this query:

SELECT O.object_id, S.name AS [schema], O.name AS [Object],
  I.index_id AS Ix_id, I.name AS IxName, I.type_desc AS IxType,
  P.partition_number AS P_No, P.data_compression_desc AS Compression
 FROM sys.schemas AS S
  JOIN sys.objects AS O
   ON S.schema_id= O.schema_id
  JOIN sys.indexes AS I
   ON O.object_id= I.object_id
  JOIN sys.partitions AS P
   ON I.object_id= P.object_id
     AND I.index_id= P.index_id
 WHERE O.TYPE = ‘U'
 ORDER BY S.name, O.name, I.index_id ;

Abbreviated result when executed in the AdventureWorks database:

object_id schema Object ix_id ixName ixType P_No Comp ---------- ------ ------ ----- ------------------- ------------- ---- ---- 1509580416 Person Person 1 PK_Person_Busines… CLUSTERED 1 NONE 1509580416 Person Person ...