March 2018
Intermediate to advanced
816 pages
19h 35m
English
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 ------------ ------- ...