SQL Server 2017 Developer's Guide

Compression and query performance

Without any further hesitation, let's start with the code. The first thing is to drop the filtered (empty) NCCI created in the previous section:

DROP INDEX NCCI_FactTest 
ON dbo.FactTest;  
GO 

The test fact table is organized as a B-tree with no additional nonclustered index, neither a rowstore nor columnstore one. The clustering key is the date. In order to make a comparison to NCCIs, let's set a baseline. First, 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 502152 KB 498528 KB 2072 KB 1552 KB ...