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

5. To enable the ROW compression on this newly created object, we can execute:

ALTER TABLE dbo.ArchiveSalesData REBUILD WITH (DATA_
COMPRESSION=ROW)

6. As the data is populated, let us execute the dbo.sp_estimate_data_

compression_savings system stored procedure to get an estimated value on
storage savings.

7. Execute the following TSQL with data_compression='ROW' :

EXEC sys.sp_estimate_data_compression_savings
@schema_name = 'dbo',
@object_name = 'ArchiveSalesData',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'ROW'

8. The result will be as follows:

object_name ArchiveSalesData
schema_name dbo
index_id 1
partition_number 1
size_with_current_compression_setting(KB) 3368
size_with_requested_compression_setting(KB) 3400
sample_size_with_current_compression_setting(KB) 3368
sample_size_with_requested_compression_setting(KB) 3400

9. 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'

10. The result will be as follows:
object_name ArchiveSalesData
schema_name dbo
index_id 1
partition_number 1
size_with_current_compression_setting(KB) 3368
size_with_requested_compression_setting(KB) 1056
sample_size_with_current_compression_setting(KB) 3400
sample_size_with_requested_compression_setting(KB) 1072