With this post I aim to highlight a feature of  SQL Server Management Studio (SSMS) regarding the DATA_COMPRESSION property when scripting objects.

First I create a table and an index with PAGE compression:

use TESTDB
GO

CREATE TABLE People
 (
 PersonID int,
 LastName varchar(200),
 FirstName varchar(200),
 Address varchar(200),
 Phone varchar(20)
 );

CREATE NONCLUSTERED INDEX [comp_index_LastName] ON [dbo].[People]
 (
 [LastName] ASC
 ) WITH (DATA_COMPRESSION = PAGE)

To make sure that the index was created with PAGE compression I am using the following query:

select i.object_id, i.name, i.index_id, i.type_desc,
 p.data_compression_desc
 from sys.indexes i, sys.partitions p
 where i.object_id = p.object_id
 and i.index_id = p.index_id
 and i.name = 'comp_index_LastName'

Now I am using the Script Index as CREATE To command from the Object Explorer of SSMS to generate the create script of the index:

USE [TESTDB]
GO

/****** Object: Index [comp_index_LastName] Script Date: 02.11.2016 19:14:34 ******/
CREATE NONCLUSTERED INDEX [comp_index_LastName] ON [dbo].[People]
(
 [LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB 
= OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

As you can see the DATA_COMPRESSION = PAGE is missing from the above definition. In case you are using the scripting feature of SSMS to recreate an Index it is important to know that DATA_COMPRESSION by default is not shown. To enable DATA_COMPRESSION to appear when scripting any object you need to open the Options Tab from the Tools menu of SSMS and und SQL Server Object Explorer you need to select Scripting. Here you can set “Script Data Compression Options” to True:

As you can see there are many other parameters you can enable or disable here that influence the output of the scripting.

Now that “Script Data Compression Options” has been set to True we can generate the Index definition again to see whether DATA_COMPRESSION is included now:

USE [TESTDB]
GO

/****** Object: Index [comp_index_LastName] Script Date: 02.11.2016 19:24:34 ******/
 CREATE NONCLUSTERED INDEX [comp_index_LastName] ON [dbo].[People]
 (
 [LastName] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB
 = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
 ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [PRIMARY]
 GO

As you can see from now on DATA_COMPRESSION will be included in the object definitions when using the scripting feature of SSMS.