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 ------------ ------- --------- --------- ---------- ------- dbo.FactTest 2279810 90256 KB 19344 KB 70192 KB 720 KB
You can see that row storage uses much more space than columnar storage. However, a regular NCI is very efficient for seeks. Let's test the queries, starting with the simple query:
SET STATISTICS IO ON; SELECT f.StockItemKey, SUM(f.TotalAmount) AS Sales FROM dbo.FactTest AS f WHERE f.StockItemKey < 30 GROUP BY f.StockItemKey ORDER BY f.StockItemKey;
This query still needed 23 LOB logical reads. If you check the execution plan, you can see that SQL Server is still using the columnstore index scan. Of course, the NCI is not very useful for this query. How about the complex query?:
SELECT f.SaleKey,
f.CustomerKey, f.Customer, cu.[Buying Group],
f.CityKey, f.City, ci.Country,
f.DateKey, d.[Calendar Year],
f.StockItemKey, f.Product,
f.Quantity, f.TotalAmount, f.Profit
FROM dbo.FactTest AS f
INNER JOIN Dimension.Customer AS cu
ON f.CustomerKey = cu.[Customer Key]
INNER JOIN Dimension.City AS ci
ON f.CityKey = ci.[City Key]
INNER JOIN Dimension.[Stock Item] AS s
ON f.StockItemKey = s.[Stock Item Key]
INNER JOIN Dimension.Date AS d
ON f.DateKey = d.Date;
Again, SQL Server needed 4,820 LOB logical reads in the test fact table. The NCI didn't improve this query; it is already optimized. Finally, let's check the point query:
SELECT CustomerKey, Profit FROM dbo.FactTest WHERE CustomerKey = 378; SET STATISTICS IO OFF;
This time, the query needed only 13 logical reads. The SQL Server query optimizer decided to use the covering NCI index, as you can see in the following screenshot, showing the execution plan for the point query for this execution:
We don't need the nonclustered index anymore, so let's drop it:
DROP INDEX NCI_FactTest_CustomerKey ON dbo.FactTest; GO
You can check the physical status of the rowgroups of the CCI using the sys.dm_db_column_store_row_group_physical_stats Dynamic Management View (DMV), as the following query shows:
SELECT OBJECT_NAME(object_id) AS table_name, row_group_id, state, state_desc, total_rows, deleted_rows FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID(N'dbo.FactTest') ORDER BY row_group_id;
Here is the result:
table_name row_group_id state state_desc total_rows deleted_rows ---------- ------------ ----- ---------- ---------- ------------ FactTest 0 3 COMPRESSED 1048576 0 FactTest 1 3 COMPRESSED 343592 0 FactTest 2 3 COMPRESSED 444768 0 FactTest 3 3 COMPRESSED 442874 0
You can see that all rowgroups are closed and compressed.
In SQL 2016 and 2017, you can also add a primary key and unique constraints to a CCI table. The following code adds a unique constraint to the test fact table. Note that you cannot add a primary key constraint because the SaleKey column is nullable:
ALTER TABLE dbo.FactTest ADD CONSTRAINT U_SaleKey UNIQUE (SaleKey); GO
You can check in the Object Explorer that the Unique constraint is enforced with help from the unique rowstore nonclustered index. The following screenshot of the Object Explorer window shows that the SaleKey column is nullable as well:
Let's test the constraint. The following command tries to insert 75,993 rows into the test fact table that already exist in the table:
INSERT INTO dbo.FactTest
(SaleKey, CustomerKey,
Customer, CityKey, City,
DateKey, StockItemKey,
Product, Quantity,
TotalAmount, Profit)
SELECT 10 * 1000000 + f.[Sale Key] AS SaleKey,
cu.[Customer Key] AS CustomerKey, cu.Customer,
ci.[City Key] AS CityKey, ci.City,
f.[Delivery Date Key] AS DateKey,
s.[Stock Item Key] AS StockItemKey, s.[Stock Item] AS Product,
f.Quantity, f.[Total Excluding Tax] AS TotalAmount, f.Profit
FROM Fact.Sale AS f
INNER JOIN Dimension.Customer AS cu
ON f.[Customer Key] = cu.[Customer Key]
INNER JOIN Dimension.City AS ci
ON f.[City Key] = ci.[City Key]
INNER JOIN Dimension.[Stock Item] AS s
ON f.[Stock Item Key] = s.[Stock Item Key]
INNER JOIN Dimension.Date AS d
ON f.[Delivery Date Key] = d.Date
WHERE f.[Sale Key] % 3 = 0;
If you execute the code, you get error 2627, violating Unique constraint. Let's recheck the status of the rowgroups:
SELECT OBJECT_NAME(object_id) AS table_name, row_group_id, state, state_desc, total_rows, deleted_rows FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID(N'dbo.FactTest') ORDER BY row_group_id;
This time, the result differs slightly:
table_name row_group_id state state_desc total_rows deleted_rows ---------- ------------ ----- ---------- ---------- ------------ FactTest 0 3 COMPRESSED 1048576 0 FactTest 1 3 COMPRESSED 343592 0 FactTest 2 3 COMPRESSED 444768 0 FactTest 3 3 COMPRESSED 442874 0 FactTest 4 1 OPEN 0 0
Although the insert was rejected, SQL Server did not close or delete the delta storage. Of course, this makes sense since this storage might become useful pretty soon for data updates. You can rebuild the index to get rid of this delta storage. The following command rebuilds the CCI, this time without archive compression:
ALTER INDEX CCI_FactTest ON dbo.FactTest REBUILD WITH (DATA_COMPRESSION = COLUMNSTORE); GO
You can check the rowgroup's status again:
SELECT OBJECT_NAME(object_id) AS table_name, row_group_id, state, state_desc, total_rows, deleted_rows FROM sys.dm_db_column_store_row_group_physical_stats WHERE object_id = OBJECT_ID(N'dbo.FactTest') ORDER BY row_group_id;
Here is the result:
table_name row_group_id state state_desc total_rows deleted_rows ---------- ------------ ----- ---------- ---------- ------------ FactTest 0 3 COMPRESSED 1048576 0 FactTest 1 3 COMPRESSED 343592 0 FactTest 2 3 COMPRESSED 444768 0 FactTest 3 3 COMPRESSED 442874 0