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 space than the NCCI. You can check the number of segments with the following query:
SELECT ROW_NUMBER() OVER (ORDER BY s.column_id, s.segment_id) AS rn, COL_NAME(p.object_id, s.column_id) AS column_name, S.segment_id, s.row_count, s.min_data_id, s.max_data_id, s.on_disk_size FROM sys.column_store_segments AS s INNER JOIN sys.partitions AS p ON s.hobt_id = p.hobt_id INNER JOIN sys.indexes AS i ON p.object_id = i.object_id WHERE i.name = N'CCI_FactTest' ORDER BY s.column_id, s.segment_id;
This time, the number of segments is 44. The CCI does not show a dictionary segment per row group; it has a global dictionary that apparently covers the whole table. How does that influence the queries?