DataGinger.com

Before writing this post, even I am not clear about the title of this post. So, I did a test scenario on AdventureWorks2012 sample database to answer the below two questions:

• Is compressing the Table the same as compressing the Clustered Index on that table?
• What to compress on a HEAP table and what to compress on a NON-HEAP table?

First let’s check if Compressing the table is the same as compressing Clustered Index or not (I haven’t found much information from BOL). I chosed “Sales.SalesOrderDetail” table from “AdventureWorks2012” sample database for the test.

And below are the test results:

In conclusion to say a table is FULLY compressed (FULLY in this context I mean compression including data and index space)From the results above, it can be concluded that Compressing the Clustered Index is nothing but compressing the Table itself. And Non-Clustered Indexes has to be compressed separately (as they are separately stored structures)

On a NON-HEAP table we have to compress:

• Either Clustered Index OR Table AND
• Non Clustered Indexes (if any)

On a HEAP table we have to compress:

• Table AND
• Non Clustered Indexes (if any)

NON-HEAP Table: Table with Clustered Index
HEAP Table: Table with no Clustered Index

Queries Used:

--1. To know the space usage
Exec Sp_spaceused '[Sales].[SalesOrderDetail]'
GO
--2. To compress Clustered Index
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
ON [Sales].[SalesOrderDetail]
REBUILD
WITH ( DATA_COMPRESSION = PAGE )
GO
--3. To remove the compression on Clustered Index
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
ON [Sales].[SalesOrderDetail]
REBUILD
WITH ( DATA_COMPRESSION = NONE )
GO
--4. To compress the table
ALTER TABLE [Sales].[SalesOrderDetail]
REBUILD WITH (DATA_COMPRESSION = PAGE)

Read Full Post »