Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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
1 | ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE) ; |
Decompress a ColumnStore Table
1 | ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE) ; |
Compress a ColumnStore Index
1 | ALTER Index ColumnStoreIndexName ON TableName REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE); |
Decompress a ColumnStore Index
1 | ALTER Index ColumnStoreIndexName ON TableName REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE); |
Comress Only Some Partitions on a ColumnStore Table
1 | ALTER TABLE ColumnstoreTableName REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1,3) ) ; |
