Skip to main content
deleted 22 characters in body
Source Link
SqlWorldWide
  • 13.6k
  • 3
  • 29
  • 54

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, IGNORE_DUP_KEY = OFF, 
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

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, IGNORE_DUP_KEY = OFF, 
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

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

Source Link
SqlWorldWide
  • 13.6k
  • 3
  • 29
  • 54

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, IGNORE_DUP_KEY = OFF, 
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