Compressed Tables and Indexes in SQL Server 2008

In this article, we will see how to use the new feature of compressing Tables and Indexes in SQL Server 2008 with some examples. The Compressed Tables and Indexes feature is newly introduced in SQL Server 2008. Creating compressed tables and indexes feature is available only in ‘SQL Server Developer and Enterprise’ editions.

Compression is now supported on ‘ROW and PAGE’ for tables and indexes. Data compression can be applied to a table, which is stored as a ‘HEAP’ or as a ‘Clustered Index’, as well as  non-clustered index and indexed view. Data compression can also be applied to partitioned tables and indexes.

To determine how compression will affect the table or index, we can use a stored procedure

 ‘sp_estimate_data_compression_savings’.

This stored procedure takes the following parameters –

• Schema Name
• Object Name
• Index ID
• Partition No
• Data compression

Let’s see an example of this stored procedure on our table, which is just a copy of the ‘Order Details’ table from Northwind. If you have not created this table, create the table first and try the following example –

USE Northwind
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'NewOrderDetails', NULL, NULL, 'ROW'

The result is shown below –



If you check the result, it shows you the current size of the table and requested compression size.

Now let’s see how to compress the above table with the following code –

ALTER TABLE NewOrderDetails REBUILD WITH (DATA_COMPRESSION = ROW)

Now let’s rerun the stored procedure ‘sp_estimate_data_compression_savings’ and check the status –



Cool! Let’s see some more examples of compressing tables and indexes.

Create Table with ROW option –



Create Table with PAGE option -



Now let’s see how to compress indexes.

Create Index with ROW option –

CREATE NONCLUSTERED INDEX Idx_CustomerDispatchDetails
    ON  CustomerDispatchDetails(City)
WITH ( DATA_COMPRESSION = ROW )

Create Index with PAGE option –

CREATE NONCLUSTERED INDEX Idx_CustomerDispatchDetails
    ON  CustomerDispatchDetails(City)
WITH ( DATA_COMPRESSION = PAGE )

You can change the compression of an index with ‘ALTER INDEX’ statement –

ALTER INDEX Idx_CustomerDispatchDetails
    ON  CustomerDispatchDetails
REBUILD WITH ( DATA_COMPRESSION = ROW )
 Now let’s create a database as shown below -

CREATE DATABASE SampleDatabase
ON  PRIMARY
( NAME = SampleDatabasePK,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\SampleDB.mdf'),
FILEGROUP FG1
( NAME = sampleDBFile1,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\sampleDBFile1.mdf'),
FILEGROUP FG2
( NAME = sampleDBFile2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\sampleDBFile2.ndf'),
FILEGROUP FG3
( NAME = sampleDBFile3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\sampleDBFile3.ndf'),
FILEGROUP FG4
( NAME = sampleDBFile4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\sampleDBFile4.ndf')
GO


Partitioned Table with Compressed Partitions

Create a Partitioned table and create compressed partitions in it. First create a partition function as shown below –



Now create a partition scheme based on partition function



Finally create a table with compressed partitions as shown below –

CREATE TABLE ProductReleaseYear
(ProductID int, ProductName varchar(100),ReleaseYear INT CONSTRAINT UK1 UNIQUE)
ON ProductReleasePS (ReleaseYear)
WITH
(
  DATA_COMPRESSION = ROW ON PARTITIONS (1),
  DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)
)

 You may also want to read Backup Compression in SQL Server 2008 and Reduce the Size of a Non-Clustered Index in SQL Server 2008