Mastering SQL Server 2017

Adding B-tree indexes and constraints

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 
------------ ------- ...