Archive data compression using Columnstore_Archive

As we all are aware about clustered columnstore index and compression that we can achive by it
In reporting system we generally query recent data more compared to older data.
So it can be acceptable for us in many cases if we can get more compression at the cost of slower query.


For this one more compression format is available.
Its columnstore_archive compression.
This compression results in slower perfromance but saves space, which is useful for archival data

Lets see an example for it

CREATE TABLE temp1

(id INT)

CREATE CLUSTERED COLUMNSTORE INDEX  idx1 ON temp1

DECLARE @i INT

SET @I = 1

WHILE @i <= 2000000

BEGIN

       INSERT INTO temp1 VALUES (@i)

       SET @i = @i+1

END

ALTER TABLE temp1 REBUILD

SP_SPACEUSED temp1

Now we will do additional compression by data_compression type columnstore_archive

ALTER TABLE temp1 REBUILD

WITH (DATA_COMPRESSION = columnstore_archive)

SP_SPACEUSED temp1  

As we can see data is compressed about 11%
This is additional compression we achieved by columnstore compression.