Configuring Data Compression

In this article we are going to configure Data compression in SQL Server 2008. Configuring data compression is very easy with just an couple of steps. The steps are below

1. Estimate the Compression Ratio (Optional)
2. Configure Data Compression

As I mentioned above first we need to estimate the compression ratio then only we can decide whether we can stick to that compression type, however this step is optional, you can configure without this step. You can configure Data compression in two ways as below

• Configure through T-SQL
• Configure through GUI

Lets configure data compression for a table in both the ways

Configure through T-SQL

*) Estimating compression ratio using page level compression. To know more about sp_estimate_data_compression_savings procedure parameters check the article http://msdn.microsoft.com/en-us/library/cc280574.aspx

/* Parameters for sp_estimate_data_compression_savings
@schema_name
@object_name
@index_id
@partition_number
@data_compression
*/
EXEC sp_estimate_data_compression_savings dbo,Compression_Test,1,NULL,PAGE

*) Configuring Data compression using page level compression

ALTER TABLE [dbo].[Compression_Test] REBUILD PARTITION = ALL
WITH
(
DATA_COMPRESSION = PAGE -- Specify the compression type here
)

*) Verify Data Compression setting for the table

SELECT partition_id,object_name(object_id) ObjectName
,data_compression_desc Data_Compression_Type
FROM sys.partitions WHERE data_compression <>0

Configure through GUI

*) Right click on the Table > Storage > Manage Compression

*) After clicking on Manage Compression, a new Data compression wizard will be started as below. Click on Next to continue

*) Select the compression type and click on Calculate button to estimate the compression ration (i.e size after compression), click on next to continue

*) You can either script it, schedule it or run immediately. In this example Im going to execute it immediately.

*) You will shown with the summary details before configuring, cross check it and click on Finish button to compress the object

*) The object will be compressed as shown below

*) To verify the compression status for the table, just right click on the table and click on properties. In the properties windows go to Storage tab there you can verify the compression status as shown below

Thats it you have now configured a table using Page level compression.