Creating Compressed Tables and Indexes in SQL Server

You should check out Creating Compressed Tables and Indexes if you haven’t already for some big space savings on your SQL Servers.  Space is cheap, true, but if you deal with a wasteful lot, you may not have the option to add a terabyte here and a terabyte there whenever somebody wants one, or an email warning gets issued from MOM, SCOM, or some other tool saying that the server is out of space.

Here’s a script that you could use to compress tables. I whipped this up in about 5 minutes, so if there’s a bug or something that doesn’t work as you like, then change it as you see fit. I use a ## table so I can see if from other windows/spids, of course.  Really impressed with how much space it can save.

DECLARE @dbname1 sysname = DB_NAME()

--Enabling databases for the vardecimal storage format is only necessary in SQL Server 2005.

EXEC sp_db_vardecimal_storage_format @dbname = @dbname1, @vardecimal_storage_format = 'ON';

GO

SET NOCOUNT ON

IF OBJECT_ID ('tempdb..##Compress') IS NOT NULL

       DROP TABLE ##Compress

GO

CREATE TABLE ##Compress
(ID int identity
, tablename sysname
, [rows] int
, sStatus tinyint default 0)

INSERT INTO ##Compress (tablename, [rows])

SELECT TABLE_SCHEMA+'.'+TABLE_NAME, [rows]

FROM INFORMATION_SCHEMA.TABLES  s

INNER JOIN (SELECT DISTINCT OBJECT_NAME(id) as id, [rows] FROM SYS.sysindexes) indexes

        ON s.TABLE_NAME = indexes.id

ORDER BY 2

DECLARE @i int;

DECLARE @tablename sysname;

DECLARE @msg varchar (1000);

DECLARE @runningCounter int;

SELECT @tablename = tablename FROM ##Compress WHERE id=(SELECT MIN(ID) FROM ##Compress)

SELECT @runningCounter= COUNT(1) FROM ##Compress

SET @i=(SELECT MIN(ID) FROM ##Compress)

SET @msg=''

WHILE (@runningCounter>0)

BEGIN

      EXEC sp_tableoption @tablename, 'vardecimal storage format', 'ON';

      EXEC ('ALTER TABLE ' + @tablename + ' REBUILD WITH (DATA_COMPRESSION = PAGE)');

          UPDATE ##Compress SET sStatus=1 WHERE ID = @i

          SELECT @i +=1

          SELECT @runningCounter-=1

          SELECT @tablename = tablename FROM ##Compress WHERE ID=@i AND sStatus=0

          SELECT @msg =  CHAR(13) + 'TABLE: ' 
                     + @tablename + ' completed at ['

                     + CAST(GETDATE() as varchar (22))        + '].  Table count remain: '

                     + CAST(@runningCounter as varchar (10))

          RAISERROR (@msg, 10,2) WITH NOWAIT

          IF (@runningCounter = 0)

                PRINT CHAR(13) + '>>>>>>>>>>>>>>>>>>>>>>  DONE! <<<<<<<<<<<<<<<<<<<<<<<<'

END

GO

Here’s the output I got after running it on a (very) small database:

TABLE: HumanResources.Employee completed at [May 18 2011  9:46PM].  Table count remain: 3

TABLE: dbo.TLOG_TEST completed at [May 18 2011  9:46PM].  Table count remain: 2

TABLE: dbo.tbl completed at [May 18 2011  9:47PM].  Table count remain: 1

TABLE: dbo.ztbl completed at [May 18 2011  9:47PM].  Table count remain: 0

>>>>>>>>>>>>>>>>>>>>>>  DONE! <<<<<<<<<<<<<<<<<<<<<<<<

Thanks for reading,

Lee

--------------------------