How To Compress ColumnStore Tables and Indexes With COLUMNSTORE_ARCHIVE Option

dbtut

5 years ago

COLUMNSTORE_ARCHIVE option was introduced with SQL Server 2014. If we want to archive ColumnStore Tables or Indexes, we can compress our columnstore tables or indexes with this option.

For example, you partitioned a columnstore table by a date column. And you want to save space by compressing old partitions. In this case, you can compress old partitions with COLUMNSTORE_ARCHIVE option. So you can save up to 15 times.

Compress a ColumnStore Table

ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ;

1	ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ;

Decompress a ColumnStore Table

ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE) ;

1	ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE) ;

Compress a ColumnStore Index

ALTER Index ColumnStoreIndexName ON TableName REBUILD  WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

1	ALTER Index ColumnStoreIndexName ON TableName REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

Decompress a ColumnStore Index

ALTER Index ColumnStoreIndexName ON TableName REBUILD  WITH (DATA_COMPRESSION = COLUMNSTORE);

1	ALTER Index ColumnStoreIndexName ON TableName REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE);

Comress Only Some Partitions on a ColumnStore Table

ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1,3) ) ;

1	ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1,3) ) ;