Microsoft SQL Server 2008 R2 Unleashed- P119:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1124 CHAPTER 34 Data Structures Indexes and Performance sp_estimate_data_compression_savings @schema_name 1schema_name1 @object_name 1object_name @index_id index_id @partition_number partition_number @data_compression 1data_compression1 You can estimate the data compression savings for a table for either row or page compression by specifying either ROW or PAGE as the value for the @data_compression parameter. You can also estimate the average size of the compressed table if compression is disabled by specifying NONE as the value for @data_compression. You can also use the sp_estimate_data_compression_savings procedure to estimate the space savings for compression on a specific index or partition. The following example estimates the space savings if page compression were applied to the big_sales table in the bigpubs2008 table versus row compression use bigpubs2008 go exec sp_estimate_data_compression_savings 1dbo sales_big null null PAGE go object_name schema_name index_id partition_number size_with_current_compression_setting KB size_with_requested_compression_setting KB sample_size_with_current_compression_setting KB sample_size_with_requested_compression_setting KB sales_big 116512 39128 40016 13440 sales_big 36648 22128 10904 6584 dbo dbo 1 1 2 1 exec sp_estimate_data_compression_savings dbo1 sales_big null null ROW go Download from Data Compression 1125 object_name schema_name index_id partition_number size_with_current_compression_setting KB size_with_requested_compression_setting KB sample_size_with_current_compression_setting KB sample_size_with_requested_compression_setting KB sales_big dbo 1 1 116512 97936 40344 33912 sales_big dbo 2 1 36648 27176 10992 8152 You can see in this example that the space savings from page compression would be significant with an estimated reduction in the size of the table itself index_id 1 from 113MB 116 512 KB to 38MB 39 128 KB a savings of more than 66 . Row compression would not provide nearly as .
