Mastering SQL Server 2017

Compression and query performance

Let's start by dropping both indexes from the demo fact table, the NCCI and the CI, to make a heap again:

USE WideWorldImportersDW; 
-- Drop the NCCI 
DROP INDEX NCCI_FactTest 
  ON dbo.FactTest; 
-- Drop the CI 
DROP INDEX CL_FactTest_DateKey 
  ON dbo.FactTest; 
GO 

Now let's create the CCI:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactTest 
  ON dbo.FactTest; 
GO 

And, of course, the next step is to 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   23560 KB  23392 KB       0 KB    168 KB

The CCI even uses slightly less ...