Mastering SQL Server 2017

Testing the nonclustered columnstore index

The following code creates an NCCI on the fact table, this time without a filter, so all data is included in the NCCI:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_FactTest 
ON dbo.FactTest 
(SaleKey, CustomerKey,  
 Customer, CityKey, City, 
 DateKey, StockItemKey, 
 Product, Quantity, 
 TotalAmount, Profit); 
GO 

So how much space is used by the test fact table now? Let's check it again with the following code:

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

Name          rows     reserved   data       index_size  unused 
------------  -------  ---------  ---------  ----------  -------
dbo.FactTest  2279810  529680 KB  498528 KB  29432 KB    1720 KB

Note the numbers. The index size is about 17 times less than the data size! ...