In this blog post, we will delve into the concept of archival compression in SQL Server. Archival compression is a feature introduced in SQL Server 2014 that allows for additional compression of data in columnstore tables and indexes. This feature is particularly useful in data warehouses where there is a mix of frequently accessed and infrequently accessed data.
Before we dive into the details, let’s understand the basics of columnstore indexes. Columnstore indexes store data in a columnar format, which provides significant performance benefits for analytical queries. SQL Server 2014 introduced support for columnstore and columnstore archival compression, allowing for even greater storage efficiency.
Archival compression is enabled on a per-table or per-partition basis. It is implemented as an extra stream compression layer on top of the columnstore index implementation. When data is written to disk, it is transparently compressed, and when read from disk, it is transparently decompressed. This compression layer provides further reduction in storage size, typically ranging from 25% to 60%+ depending on the data.
To apply archival compression, you can use T-SQL commands. Let’s take a look at some examples:
-- Adding Archive bit for single Partition
ALTER TABLE tbl_myColumnStore REBUILD PARTITION = 1 WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );
-- Adding Archive bit for all Partitions
ALTER TABLE tbl_myColumnStore REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );
-- Adding Columnstore for all partitions and Archive for specific partitions
ALTER TABLE tbl_myColumnStore REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (1, 3));
These commands demonstrate how you can apply archival compression to specific partitions or to all partitions in a table. The resulting data will continue to be compressed with columnstore compression, providing efficient storage and improved query performance.
If you want to decompress the data that has been compressed with archival compression, you can use the following command:
-- Turning Archive bit off and applying Columnstore compression for all Partitions
ALTER TABLE tbl_myColumnStore REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = COLUMNSTORE );
This command removes the archival compression and applies columnstore compression to all partitions in the table.
As we conclude this blog post, we would like to hear from you. Are you using archival columnstore compression in your environments? How much compression are you achieving, and what are the space savings? Share your experiences with us as we continue to learn together.