estimate data saving by compression

Today I got a mail for how to get space saving estimation using compression


Well compression is supported in SQL Server 2008 Enterprise and Developer Edition.



We can have two type of compressions 1) Row 2) Page. I will give details in brief in coming articles. Our question is to know how much space can we save using compression?  We can get this answer using

sp_estimate_data_compression_savings procedure.

Syntax for proc is
sp_estimate_data_compression_savings
      @schema_name
     ,@object_name
     ,@index_id
     ,@partition_number
     ,@data_compression



sp_estimate_data_compression_savings
      @schema_name ='SALES'
     ,@object_name  ='SalesOrderDetail'
     ,@index_id =NULL
     ,@partition_number =NULL
     ,@data_compression = 'ROW'



    
sp_estimate_data_compression_savings
      @schema_name ='SALES'
     ,@object_name  ='SalesOrderDetail'
     ,@index_id =NULL
     ,@partition_number =NULL
     ,@data_compression = 'PAGE'
   


If index id is not passed it will show result for all indexes.
If partition id is null result for all partitions will be displayed.

It takes sample data into tempdb and calculates space saving using compression , and then gives result
Actual result may be differ on row size and fill factor  of index. But we can get rough estimation of space
saving using compression


Result will show space used  for current data as well as after compression .
Here in sample we can see space saving using page compression is more than row . However each type has its own limitation , we can take decision depending on our requirement .