Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 238

11. From the previous scenario, the PAGE level compression is a definite savior that
shows additional benefits on the storage level.
12. At any time, the compression type can be configured on the table using the following
TSQL statement:
ALTER TABLE dbo.ArchiveSalesData REBUILD WITH (DATA_
COMPRESSION=PAGE)

13. The value used in the DATA_COMPRESSION will take care of the compression of
clustered indexes and heaps.
14. Now, let us enable the compression on non-clustered indexes using the CREATE
INDEX and ALTER INDEX statement.
15. Using the following TSQL statement, let us compress non-clustered indexes with a
PAGE level compression:
CREATE NONCLUSTERED INDEX NCI_ArcSalesData_ProdIDSalesQty
ON dbo.ArchiveSalesData (ProductID,SalesQty)
WITH (DATA_COMPRESSION = PAGE)

16. As we have created the index, let us see how far we are saving the storage with this
new object. Execute the following TSQL with data_compression='PAGE' :
EXEC sys.sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'ArchiveSalesData',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'PAGE'

17. The results are as follows (observe the results of two objects):

object_ schema_ index_ Partition size_with_

current_

size_with_ sample_size_

with_current_
compression_
setting(KB)

sample_
size_with_
requested_
compression_
setting(KB)

name name id _number requested_

compression_
setting(KB)

compression_

setting(KB)

ArchiveSalesData dbo 1 1 3368 1056 3400 1072

ArchiveSalesData dbo 2 1 992 848 952 816

18. At any time, the compression type can be configured on the table using the PAGE or
ROW value at the DATA_COMPRESSION option.

This completes the steps to implement compressed storage features for tables and indexes.