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
Syntax for proc is
,@data_compression = 'ROW'
,@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 .