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.

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts