How to compress rowstore tables in SQL Server

Introduction

This T-SQL script will demonstrate how to compress rowstore tables in an instance. It will be realized by generating the data compression code in a table rather than doing the compression automatically on the first click. If you want to compress the rows or pages for all the tables, please copy the code generated in the table and run it.

Scenarios

As some people want to do data compression for all the tables in an instance, we provide this script to make your job a little easier. The script contains a stored procedure in which a compression type parameter will be provided by the users. The parameter can be ROW, PAGE and NONE. Code will be generated in a table according to the compression type you entered.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.

2. Select the specified database and create a "New Query", copy the code from DataCompresssionForTables.sql, paste it and run the script.

3. Execute the stored procedure with a parameter which can be ROW, PAGE and NONE.
For example: EXEC Compression 'ROW'

After the script and example finishes running, we'll get the following figure:

Here are some code snippets for your reference.

SQL

Edit|Remove

IF(UPPER(@Compressiontype) IN('ROW','PAGE','NONE'))
BEGIN
SELECT 'ALTER TABLE [' + DBName + '].'+'[' +SchemaName +'].['+TableName +'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+ @Compressiontype + ')'  FROM @table
END
ELSE
PRINT 'The compression type you entered is not NONE,ROW or PAGE, please re-enter!'
END

IF(UPPER(@Compressiontype) IN('ROW','PAGE','NONE')) 
BEGIN 
SELECT 'ALTER TABLE [' + DBName + '].'+'[' +SchemaName +'].['+TableName +'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = '+ @Compressiontype + ')'  FROM @table 
END 
ELSE 
PRINT 'The compression type you entered is not NONE,ROW or PAGE, please re-enter!' 
END

Prerequisites

SQL Server 2005 or higher version

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.