Data Compression Procedure for All Tables in SQL Server

Hello everyone,

In this article, I will give information about data compression procedure for all tables in SQL Server.

There are two types of data compression.

Row Level Data Compression: A compression method that converts fixed-length data types to variable-length data types and frees up free space. It also saves additional space by ignoring zero and null values. In contrast, it can fit more rows in a single data sheet.

Page-Level Data Compression: A compression method that starts with row-level data compression and then adds a prefix and a dictionary compression for data pages.

The following procedure expects either ROW or PAGE parameter. Which compression type you want to use, you can run that parameter by writing it to the procedure.

CREATE PROCEDURE DataCompressionProcedureforAllTables 
(@compression_method CHAR(4))
AS
SET NOCOUNT ON;
BEGIN
    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
    SELECT TABLE_SCHEMA,
           TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE LIKE 'BASE%'
          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
    BEGIN
        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 = @compression_method;
        FETCH NEXT FROM c_sch_tb_crs
        INTO @schema_name,
             @table_name;
    END;
    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;
END;
SET NOCOUNT OFF;


--Usage 
--Run after selecting the relevant database. Otherwise, the responsibility belongs to you.

EXEC DataCompressionProcedureforAllTables @compression_method= 'PAGE';
GO

Good luck to everyone in business and life.