SQL Server 2017 Developer's Guide

Using archive compression

You might remember that there is still one option left for columnar storage compression—archive compression. Let's turn it on with the following code:

ALTER INDEX CCI_FactTest 
 ON dbo.FactTest 
 REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE); 
GO 

You can imagine what comes next; recheck the space used by the test fact table:

EXEC sys.sp_spaceused N'dbo.FactTest', @updateusage = N'TRUE'; 
GO 

The result is as follows:

Name          rows     reserved   data       index_size  unused 
------------  -------  ---------  ---------  ----------  -------
dbo.FactTest  2279810   19528 KB   19336 KB      0 KB    192 KB

The LZ77 algorithm added some additional compression. Compare the data size now with the initial data size when the data size was 498,528 KB; now ...