ColumnStore Archive Compression in SQL Server

 Database usually is the biggest investment in the solution. If you want to make your system fast and reliable, try to use as much database features as possible. ColumnStore index is a column-based data storage technology for storing, logically organized as a table with rows and columns, works on column-based query processing and allows running analytic queries concurrently with data loads. SQL Server provides us more extra space to further reduce the size of columnstore data by configuring an additional compression called archival compression.

However, the data compression will depend on the volume of data which is going to be stored. Columnstore indexes are based on RLE (Run-Length Encoding) compression technique which is suitable for repetitive values in our data table. To perform archival compression, SQL Server runs the Microsoft XPRESS compression algorithm on the data. There are two data compression techniques for adding or removing archival compression which are:

• ColumnStore_Archive Data Compression is used to compress columnstore data with archival compression.
• ColumnStore Data Compression is used to decompress archival compression. This resulting data will continue to be compressed with columnstore compression.

To understand this functionality, we are created a table called ColumnStoreArchival having two columns, one column would be numeric and another one would be string type. We are going to insert 100000 records in this table and will measure the occupied space also as given below:

------ Create table CREATE TABLE ColumnStoreArchival   (      Id INT,                  DataValue VARCHAR(50)    ) ----- Declare local variable to incremental with default value DECLARE @ictr INT =1 ----- Insert Value into the ColumnStoreArchival WHILE @ictr <= 100000   BEGIN   ----- Insert value and casting for int to varchar       INSERT INTO ColumnStoreArchival (Id, DataValue)       VALUES (@ictr, 'ColumnStoreArchival for:'+ Cast(@ictr as Varchar(10)))   ----- set incremental here       SET @ictr = @ictr + 1   END   ---- after load values check the space of the table SP_SpaceUsed ColumnStoreArchival

In the above table, you can see that we did not create any index. To compress the data we are going to create columnstore index on this table as given below:

----- create clustered index on the table CREATE CLUSTERED COLUMNSTORE INDEX Ind_ColumnStore ON dbo.ColumnStoreArchival   ---- after creating columnstore index on table   ---- check occupied space again SP_SpaceUsed ColumnStoreArchival

You can see that after creating the columnstore index on the table, reserved and data size have been decreased accordingly. On this table, we need to apply data compression technique also as given below:

---- Alter columnindex with data compression technique ---- by using columnstore_archive ALTER INDEX Ind_ColumnStore ON dbo.ColumnStoreArchival REBUILD WITH (data_compression = columnstore_archive)   ---- after applying data compression technique on table   ---- check occupied space again SP_SpaceUsed ColumnStoreArchival

Wow, you can see that after applying the data compression = columnstore_archive technique on the table having columnstore index, reserved and data size have been decrease dramatically in the above example.

Performance

Compressing columnstore indexes with archival compression will cause the index to perform slower than columnstore indexes that do not have the archival compression. Use archival compression only when you can afford to use extra time and CPU resources to compress and retrieve the data.

The benefit of slower performance is reduced storage which is useful for data that is not frequently accessed. For example, if you have a partition for each month of data, and most of your activity is for the most recent months, you could archive older months to reduce the storage requirements.
To know more on Data Compression-
Considerations for When You Use Row and Page Compression
ColumnStore Archive Compression in SQL Server
How does Row Compression work?
What is Data Compression in SQL Server?

What is Page Compression in SQL Server?
References:https://msdn.microsoft.com/en-us/library/cc280449(v=sql.120).aspx