May 11, 2011

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 –

clip_image001[4]

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 –

clip_image001[6]

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

Create Table with ROW option

SQL Server 2008 Compression

Create Table with PAGE option -

SQL Server 2008 Compression

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 –

image

Now create a partition scheme based on partition function

image

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter



 
  Feedback:

comments

1 Response to "Compressed Tables and Indexes in SQL Server 2008"
  1. Pravesh Singh said...
    December 24, 2011 at 6:56 AM

    This is one of the best answer so far, I have read online.Just useful information. Very well presented. I had found another good collection of index in sql server over internet.
    please check out this link...
    Index in sql server

    Thanks

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions