Sql-server – how to add data compression to a primary key of a table

compressionprimary-keyscriptingsql serversql-server-2016

We work with tables with over 300GB of size, a small percentage of them tables use sparse columns
although that doesn't always agree with CPU utilization as this question shows.

For the tables that don't use sparse columns I want to implement data compression – either page or row
depending on the findings of procedure sp_estimate_data_compression_savings
or this script:

T-SQL Script for Estimating Compression Savings

I have a routine that generate the create index scripts that works fine.

when I create this table as an example:

IF OBJECT_ID('[DBO].[t1]') IS NOT NULL 
DROP TABLE [DBO].[t1] 
GO
CREATE TABLE [DBO].[t1] ( 
[a]  INT                              NOT NULL,
CONSTRAINT   [PK__t1__3BD0198E286DEFE4]  
PRIMARY KEY CLUSTERED    ([a] asc))

My create index scripts gives me:

USE [ontime_new_test];
ALTER TABLE [dbo].[t1] 
ADD  CONSTRAINT [PK__t1__3BD0198E286DEFE4] 
PRIMARY KEY CLUSTERED (  [a] ASC  )  
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , 
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, 
DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  

Question:

Is there a way to apply a data compression to this table without dropping the primary key?

Basically I want to get this done (note the data_compression=PAGE) without having to drop any constraint first:

USE [ontime_new_test];
ALTER TABLE [dbo].[t1] 
ADD  CONSTRAINT [PK__t1__3BD0198E286DEFE4] 
PRIMARY KEY CLUSTERED (  [a] ASC  )  
WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , 
IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, 
DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  

Best Answer

Is there a way to apply a data compression to this table without dropping the primary key?

Yes you just need to run below command to rebuild the clustered index (in your case it is also PK):

ALTER INDEX [PK__t1__3BD0198E286DEFE4] on TABLE [t1] 
REBUILD with (ONLINE = ON, DATA_COMPRESSION = PAGE)

"ONLINE = ON" clause can help greatly reduce locking of table that is being rebuilt