Posts Tagged ‘page compression’

SQL – Page compression

November 3, 2011 Leave a comment

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: