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 

The result is as follows:

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 ...

Get SQL Server 2017 Developer's Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.