SQL Server Data Compression Storage Savings for all Tables

By:   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Compression


In SQL Server 2008, we were introduced to Data Compression which can reduce data storage needs and potentially improve overall data access in the Enterprise and Developer editions. I have heard there are two types of compression available. Can you please explain them to me? However, my bigger question is I have databaeses that were originally developed in SQL Server 7.0, 2000 and 2005, so which tables should I compress? Do you have any scripts I can use to figure out the best candidates for compression? Check out this tip to learn more.


Those are great questions. To answer your first one related to the types of data compression, there are two methods:

  • Row-level Data Compression: a compression method that turns fixed length data types into variable length data types, freeing up empty space. It also ignores zero and null values, saving additional space. In turn, more rows can fit into a single data page.
  • Page-level Data Compression: a compression method that starts with row-level data compression, then adds a prefix and a dictionary compression for data pages.

To address your second question, I have coded a stored procedure called usp_tables_compress_report that loops over all of the objects in your database and executes the sp_estimate_data_compression_savings stored procedure. My stored procedure expects two parameters either ROW or PAGE. The valuable data to answer your second question is in the last column (avg_size_saving) of the stored procedure's result set. Let's jump into the code and review a sample result set.

CREATE PROCEDURE usp_tables_compress_report (@compress_method char(4))
DECLARE @schema_name sysname, @table_name sysname
CREATE TABLE #compress_report_tb 
(ObjName sysname,
schemaName sysname,
indx_ID int,
partit_number int,
size_with_current_compression_setting bigint,
size_with_requested_compression_setting bigint,
sample_size_with_current_compression_setting bigint,
sample_size_with_requested_compression_setting bigint)
DECLARE c_sch_tb_crs cursor for 
AND TABLE_CATALOG = upper(db_name())
OPEN c_sch_tb_crs
FETCH NEXT FROM c_sch_tb_crs INTO @schema_name, @table_name
WHILE @@Fetch_Status = 0 
INSERT INTO #compress_report_tb
EXEC sp_estimate_data_compression_savings
@schema_name = @schema_name,
@object_name = @table_name,
@index_id = NULL,
@partition_number = NULL,
@data_compression = @compress_method 
FETCH NEXT FROM c_sch_tb_crs INTO @schema_name, @table_name
CLOSE c_sch_tb_crs 
DEALLOCATE c_sch_tb_crs
SELECT schemaName AS [schema_name]
, ObjName AS [table_name]
, avg(size_with_current_compression_setting) as avg_size_with_current_compression_setting
, avg(size_with_requested_compression_setting) as avg_size_with_requested_compression_setting
, avg(size_with_current_compression_setting - size_with_requested_compression_setting) AS avg_size_saving
FROM #compress_report_tb
GROUP BY schemaName,ObjName
ORDER BY schemaName ASC, avg_size_saving DESC 
DROP TABLE #compress_report_tb

Here is a sample exeuction and result set:

USE Northwind 
EXEC usp_tables_compress_report @compress_method = 'PAGE'

data compression in sql server 2008
Next Steps
  • Review the stored procedure from this tip and check out the compression improvements possible with some of your databases that are running on SQL Server 2008 Enterprise or Developer edition.
  • Check out the remainder of the SQL Server Data Compression tips.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, March 10, 2015 - 8:47:27 AM - dan Back To Top (36478)

HI Eli,

I ran the SP against Master and tried to see compression of Adventure works it's not working!!! 

Friday, July 25, 2014 - 7:20:03 AM - BRIJ Back To Top (32878)

Hello Eli,

Do you have any blog created for Index saving?


SQL Server Data Compression Storage Savings for all Indexes ? If yes please let me know


Tuesday, May 6, 2014 - 8:39:17 AM - dab Back To Top (30629)

can you do order by ave_size_with_current_compression_setting desc?

Friday, March 23, 2012 - 12:28:53 PM - Fred Pizarro Back To Top (16594)

Very good sample ... thanks !!!

get free sql tips
agree to terms