SQL Server Stored Procedure to Compress Non Partitioned Tables

By:   |   Comments (3)   |   Related: > Compression


My SQL Server database has some very big tables that can be compressed. The tables are simple, non-partitioned tables and I want a quick tool to compress them. I do not want to shrink the whole database since it will apply a global lock on the whole database, including both the partitioned and non-partitioned objects. How can this task be achieved?


The solution shown here includes constructing a T-SQL stored procedure (I called it dbo.usp_CompressNonPartitionedTables). The stored procedure gets the schema name, the compression method and a minimal space threshold value. The procedure loops over all the non-partitioned tables in the database that belong to the given schema that are not already compressed by any method. Note - The partition number is always 1 for all non-partitioned tables and the data compression column is always 0 for the non-compressed tables. Therefore, the list of tables that meet the above conditions can be retrieved by executing the following query:

FROM sys.partitions p
  ,sys.tables t
  ,sys.schemas s
WHERE p.object_id = t.object_id
  AND p.partition_number = 1
  AND t.schema_id = s.schema_id
  AND s.NAME = 'dbo'
  AND p.data_compression = 0

Executing this code on the Northwind sample database will produce the following results:

Order Details

The procedure will execute the sp_estimate_data_compression_savings system stored procedure. This procedure estimates the amount of disk savings (in KB) and predicts what happens if we enable row or page level compression, which are the two methods of SQL Server compression. These compression options are available for all kinds of objects: tables, indexes, and file groups. If the difference (in KB) between size_with_current_compression_setting and size_with_requested_compression_setting exceeds the given space parameter threshold value, then the given parameter compression method is applied using dynamic T-SQL.

SQL Server Stored Procedure to Compress Tables

CREATE PROCEDURE dbo.usp_CompressNonPartitionedTables (
  @compression_mode VARCHAR(10),
  @schema VARCHAR(30),
  @size_thresholdKB INT )


 set nocount on
 DECLARE @tablename VARCHAR(60)
 DECLARE @expectedSavingKB BIGINT
 FROM sys.partitions p ,sys.tables t ,sys.schemas s
 WHERE p.object_id = t.object_id AND p.partition_number = 1 AND t.schema_id = s.schema_id
  AND s.NAME = @schema AND p.data_compression = 0);

 -- Create results table
 CREATE TABLE #estimated_savings (
  [object_name] SYSNAME
  ,[schema_name] SYSNAME
  ,[index_id] INT
  ,[partition_number] INT
  ,[size_with_current_compression_settingKB] BIGINT
  ,[size_with_requested_compression_settingKB] BIGINT
  ,[sample_size_with_current_compression_settingKB] BIGINT
  ,[sample_size_with_requested_compression_settingKB] BIGINT );

 OPEN cur 
 FETCH NEXT FROM cur INTO @tablename
  INSERT INTO #estimated_savings
  EXECUTE sp_estimate_data_compression_savings @schema_name = @schema
  ,@object_name = @tablename
  ,@index_id = NULL
  ,@partition_number = NULL
  ,@data_compression = @compression_mode;

  SELECT @expectedSavingKB =  ([size_with_current_compression_settingKB] - 
  FROM #estimated_savings

  IF (@expectedSavingKB > @size_thresholdKB)
   SET @tsql = 'ALTER TABLE ' + @schema + '.[' + @tableName + ']' +
    ' rebuild WITH( DATA_COMPRESSION = ' + @compression_mode + ' )'
   EXEC (@tsql)
  TRUNCATE TABLE #estimated_savings
  FETCH NEXT FROM cur INTO @tablename
 CLOSE cur
 DROP TABLE #estimated_savings
 set nocount off

Stored Procedure Notes

  1. Only the Enterprise (or Developer) Editions of SQL Server 2008 through SQL Server 2016 support this solution.
  2. Rebuilding and compressing is an off-line operation. It must be done when the table is off-line and for the whole duration of the process.
  3. Execute this procedure only if the amount of storage space saved is considerably large.
  4. In the example shown here the threshold selected is small (15KB), usually it's not necessary to compress such small amount, it is done here only for demonstration purposes.

Sample Stored Procedure Execution

Let's take the northwind sample database and execute the stored procedure. Prior to the stored procedure execution, all the user tables that reside in the dbo schema are non-partitioned and also not compressed. (data_compression column = 0).

We execute the procedure like this:

USE Northwind

EXEC dbo.usp_CompressNonPartitionedTables 
     @compression_mode = 'ROW',
     @schema = 'dbo',
     @size_thresholdKB = 15

Now we check the results by running the following query in the northwind database:

FROM sys.partitions p
  ,sys.tables t
  ,sys.schemas s
WHERE p.object_id = t.object_id
  AND p.partition_number = 1
  AND t.schema_id = s.schema_id
  AND s.NAME = 'dbo'
  AND p.data_compression <> 0

The result is: Orders. That means that the orders table has been compressed using ROW level compression and the storage saved is greater than 15KB.

Further notes: The procedure was tested on SQL Server 2012 Enterprise Edition.

Next Steps
  • Consider using this procedure if you want to compress non-partitioned tables without a whole database shrink.
  • Learn more about SQL Server Compression.

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

Wednesday, June 14, 2023 - 1:31:44 AM - vasanth Back To Top (91283)
how to multiple table declare the single variable stored procedure?

Friday, August 12, 2016 - 1:02:29 PM - SAinCA Back To Top (43114)

Could you briefly give your rationale for stating a compression_mode of ROW.  Would it not be more advantageous to run the estimate with ROW then PAGE and take the better option?  Having just run said SP on a 16M row table, small by this DB's standards, ROW reduced from 2.1GB to 1.55, but PAGE from 2.1 to 0.55.  Similaraly large savings of 1GB per index over the 5 non-CL ix's would appear, in this case, to recommend PAGE over ROW by a healthy margin.

Are there some unmentioned downsides to PAGE compression that you have found in practice?  I'm about to enlarge our use of compression on a critical customer's DB that was giving us huge disk Q numbers and thrashed SSDs until we applied PAGE compression to its, now, 914M+ rows in 3 indexes, and would hate to make a poor choice...

Thanks for the code - may well find it as the basis for our automation, with credit due you.

Friday, August 12, 2016 - 11:29:49 AM - Steve Smith Back To Top (43112)

Despite your note 2 in Stored Procedure Notes, when using Enterprise Edition the Compression can indeed be performed WITH (ONLINE=ON).  

There are a few exceptions (such as XML indexes, NVARCHAR(MAX) columns etc q.v. https://msdn.microsoft.com/en-us/library/ms190981(v=sql.105).aspx), but generally this is an easy option and essential on large 24/7 databases.


get free sql tips
agree to terms