Working at an ISV like NewsGator Technologies, I get to work on products like Social Sites for SharePoint 2010, that use SQL Server 2008 or greater (along with SharePoint 2010). We don’t require SQL Server 2008 Enterprise Edition, but we try to seamlessly take advantage of it if it is present, with things like Data Compression, which is an Enterprise Edition only feature in SQL Server 2008 and above.

We have a table that is a perfect candidate for data compression (since it has a number of int and bigint data type columns which compress well, and the data never changes after the initial insert), so I thought I would add data compression support for the clustered index of the table. This will reduce the space used by this table by about 60%, and reduce the write I/O required by a similar amount. Remember, you cannot create or rebuild (or compress) a clustered index in SQL Server 2008 R2 in ONLINE mode if you have any BLOB data types in the table.

Here is a code sample showing how to check for Enterprise Edition, then check to see if the clustered index is already compressed, and then finally compress the clustered index in ONLINE mode, limiting the number of processor cores used for the index compression to 2. This will make the compression take slightly longer, but will reduce the concurrency impact on the system.

-- Adding Data Compression in SQL Server 2008 and 2008 R2
-- Glenn Berry
-- October 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry

    -- Check for Enterprise Edition, and use Page data compression on the clustered index if we have it
   IF SERVERPROPERTY('EngineEdition') = 3
          BEGIN
               -- Check to see if we already have any compression on the index
               IF (SELECT data_compression
                    FROM sys.partitions
                   WHERE index_id < 2
                    AND OBJECT_NAME(object_id) = N'NM_FeedRetrieveHistory') = 0
                   
                    BEGIN
                       -- Use Page Compression on the clustered index if we
                        -- have SQL Server 2008 Enterprise Edition or greater
                       ALTER TABLE[dbo].[NM_FeedRetrieveHistory] REBUILD PARTITION = ALL
                       WITH(DATA_COMPRESSION = PAGE,ONLINE = ON,MAXDOP = 2);
                   END
          END