Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity.

However, the server has 60GB of memory (of which 48 is dedicated to SQL Server service), so all data didn't fit in memory and the SAN is not the fastest one around.
So I decided to give compression a go, since we use Enterprise Edition anyway.

This is the code I use to compress all tables with PAGE compression.

DECLARE @SQL VARCHAR(MAX)

DECLARE curTables CURSORFOR

SELECT'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(object_id))

+'.'+QUOTENAME(OBJECT_NAME(object_id))

+' REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'

FROMsys.tables

OPEN curTables

FETCHNEXT

FROM curTables

INTO @SQL

WHILE@@FETCH_STATUS= 0

BEGIN

IF @SQL ISNOTNULL

RAISERROR(@SQL, 10, 1)WITHNOWAIT

FETCHNEXT

FROM curTables

INTO @SQL

END

CLOSE curTables

DEALLOCATE curTables

Copy and paste the result to a new code window and execute the statements. One thing I noticed when doing this, is that the database grows with the same size as the table. If the database cannot grow this size, the operation fails.
For me, I first ended up with orphaned connection. Not good.

When this was done, I noticed that the 90GB database now only was 17GB. And most important, complete database now could reside in memory!

After this I took care of the administrative tasks, backups. Here I copied the code from Management Studio because I didn't want to give too much time for this. The code looks like (notice the compression option).