Feeds:
Posts
Comments

Archive for the ‘Compression’ Category

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:

Space Usage rows reserved data index_size unused
prior to compression 121317 17920 KB 10896 KB 6528 KB 496 KB
after compressing Clustered Index (CI) 121317 12224 KB 5240 KB 6528 KB 456 KB
after removing compression on CI 121317 17856 KB 10896 KB 6528 KB 432 KB
after Table compression 121317 12224 KB 5240 KB 6528 KB 456 KB

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)
Advertisements

Read Full Post »