how to add data compression to a primary key of a table?

Question

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] 

Answer 1

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

Answer 2

Reference: ALTER INDEX (Transact-SQL)

Yes you can data compression to this table without dropping the primary key. With keeping all the options you mentioned, following script will compress the table. This script will NOT compress any non-clustered index.

ALTER INDEX [PK__t1__3BD0198E286DEFE4] on TABLE [t1] 
REBUILD WITH (PAD_INDEX = OFF, FILLFACTOR = 100, 
STATISTICS_NORECOMPUTE = OFF, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION= PAGE, ONLINE=ON )

I added option ONLINE=ON but you can also perform offline. Read `Online Index Operations