Home > SQL Server > SQL – Page compression

SQL – Page compression

Compression of data files is one of the familiar tasks in the database maintenance tasks. Data compression of data files is the process of arranging the data inside of the files.
We have 3 different compressions as
1. Page compression
2. Row compression
3. Dictionary compression
Many times I got a situation of databases without implementation of any compression even in the production systems which motivated me to prepare one handy script for future reference as I have used this script many times while playing with databases including production environments.
Here presenting a dynamic script to convert any database into any compression with comments

DECLARE @Database VARCHAR(255)   

    FOR SELECT  name
        FROM    master.dbo.sysdatabases
        WHERE   name IN ( 'SANDBOX', 'BITEST' )   -- Specify the database names
        ORDER BY 1  

OPEN DatabaseCursor  

FETCH NEXT FROM DatabaseCursor INTO @Database  

        SET @cmd =  'DECLARE TableCursor CURSOR FOR SELECT table_catalog 
                  + ''.'' + table_schema + ''.'' 
                  + table_name as tableName FROM ' + @Database
                  + '.INFORMATION_SCHEMA.TABLES 
                    WHERE table_type = ''BASE TABLE'''   

   -- Create table cursor  
        EXEC ( @cmd
        OPEN TableCursor   

        FETCH NEXT FROM TableCursor INTO @Table   
        WHILE @@FETCH_STATUS = 0   
                SET @cmd = 'ALTER TABLE  ' + @Table
                         + ' REBUILD WITH (DATA_COMPRESSION =  PAGE) '  -- Row and Dictionary
                EXEC ( @cmd

                FETCH NEXT FROM TableCursor INTO @Table   

        CLOSE TableCursor   
        DEALLOCATE TableCursor  

        FETCH NEXT FROM DatabaseCursor INTO @Database  
CLOSE DatabaseCursor   
DEALLOCATE DatabaseCursor 

Points to consider:
1. Consider the comments section and modify the parameters according to the situation of databases
2. The database will go into lock mode so consider the maintenance window appropriately
3. Consider this script only as a reference and I recommend understanding the logic in-depth before going to use the script
4. I haven’t presented any executed results as I used many times so I am confident on the script that it will work in any environment

Categories: SQL Server Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: