SQL Server compression – don’t loose it

If you are using the Enterprise Edition of SQL server 2008 or later, you have the option to use Data compression and it can be used to improve performance in some scenarios. It is easy to apply datacompression to an existing index. The script below rebuilds an existing index on Employee table in AdventureWorks:

ALTER INDEX [AK_Employee_rowguid] ON [HumanResources].[Employee] REBUILD PARTITION = ALL 
WITH (
	PAD_INDEX = OFF, 
	STATISTICS_NORECOMPUTE = OFF, 
	SORT_IN_TEMPDB = OFF, 
	IGNORE_DUP_KEY = OFF, 
	ONLINE = OFF, 
	ALLOW_ROW_LOCKS = ON, 
	ALLOW_PAGE_LOCKS = ON, 
	DATA_COMPRESSION = PAGE)

But when you have applied data compression, make sure you don’t loose it by mistake. Lets say you wants to add another column to the index and you right click the index and klick the menu Script Index /Drop and Create to / New Query Window, and then you add your column to the create script and execute the query.

If you are unlucky the compression is lost. Why? Well it has to to with an option in Management Studio. Click the menu Tools / Options, scroll down to the “SQL Server object explorer” and look at the “scripting” options.  The default setting for “Script Data Compression Options” is actually false! Change the setting to True and close the dialog.

/Håkan Winther
COO / Senior SQL Server consultant