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 it is only 19,336 KB. The compression rate is more than 25 times! This is really impressive. Of course, you'd expect test queries to be even more efficient now. For example, here is the simple query:

SET STATISTICS IO ON; 
SELECT f.StockItemKey, 
 SUM(f.TotalAmount) AS Sales 
FROM dbo.FactTest AS f 
WHERE f.StockItemKey < 30 
GROUP BY f.StockItemKey 
ORDER BY f.StockItemKey; 

This time, SQL Server needed only 23 LOB logical reads. The next query to test is the complex query:

SELECT f.SaleKey, 
  f.CustomerKey, f.Customer, cu.[Buying Group], 
  f.CityKey, f.City, ci.Country, 
  f.DateKey, d.[Calendar Year], 
  f.StockItemKey, f.Product,  
  f.Quantity, f.TotalAmount, f.Profit 
FROM dbo.FactTest AS f 
  INNER JOIN Dimension.Customer AS cu 
    ON f.CustomerKey = cu.[Customer Key] 
  INNER JOIN Dimension.City AS ci 
    ON f.CityKey = ci.[City Key] 
  INNER JOIN Dimension.[Stock Item] AS s 
    ON f.StockItemKey = s.[Stock Item Key] 
  INNER JOIN Dimension.Date AS d 
    ON f.DateKey = d.Date; 

This time, SQL Server needed 4,820 LOB logical reads in the test fact table. It can't get much better than this for scanning all of the data. And what about the point query?:

SELECT CustomerKey, Profit 
FROM dbo.FactTest 
WHERE CustomerKey = 378; 

This time, it used 410 LOB logical reads. This number can still be improved.