Home » SQL Server » Implementation of Data Compression – SQL Server

Implementation of Data Compression – SQL Server

Data Compression

Data Compression is feature of Microsoft SQL Server to reduce the size of table on the basis of Duplicates, Null & Zeroes. It’s a process of reducing size of database & its objects by increasing CPU cycle and reducing I/O effort.

Types of Database Compression

· Row Compression

· Page Compression

You can download Data Compression SQL Server white Paper – https://mssqlfun.files.wordpress.com/2015/10/data-compression-sql-server-white-paper1.pdf

Implementation of Data Compression

Row Compression	Page Compression
T-SQL Command

ALTER TABLE <TABLE_NAME> WITH (DATA_COMPRESSION=ROW)	ALTER TABLE <TABLE_NAME> WITH (DATA_COMPRESSION=PAGE)
SSMS
Step 1 – Open Data Compression wizard. Right Click on Table > Storage > Manage Compression

Step 2 – Select Compression Type & you can click on calculate to check the impact


You can use below system store procedure to estimate the compression results
EXEC sp_estimate_data_compression_savings ‘Schema’,’Table_Name’,Null,Null,’Type_of_Compression’



Step 3 – Select Option if you want to run it immediately or later by generating script

Step 4 – Final Configuration window before you give final go ahead

Step 5 – Compression implemented successfully