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 ...
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.