There is still one query, the point query, which needs additional optimization. In SQL Server 2016 and 2017, you can create regular, rowstore B-tree nonclustered indexes on a clustered columnstore index, on a table that is organized as columnar storage. The following code adds a nonclustered index with an included column, an index that is going to cover the point query:
CREATE NONCLUSTERED INDEX NCI_FactTest_CustomerKey ON dbo.FactTest(CustomerKey) INCLUDE(Profit); GO
Before executing the queries, let's check the space used by the demo 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 ------------ ------- ...