Without any further hesitation, let's start with the code. The first thing is to drop the filtered (empty) NCCI created in the previous section:
DROP INDEX NCCI_FactTest ON dbo.FactTest; GO
The test fact table is organized as a B-tree with no additional nonclustered index, neither a rowstore nor columnstore one. The clustering key is the date. In order to make a comparison to NCCIs, let's set a baseline. First, 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 502152 KB 498528 KB 2072 KB 1552 KB
You can measure IO with the SET STATISTICS IO ON command. In addition, you can turn on the actual execution plan. Here is the first sample query; let's call it 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;
The query did a full clustered index scan, and there were 63,601 logical reads. You may also notice in the execution plan that only row mode operators were used.
The next query involves multiple joins; let's call it 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;
This time, SQL Server created a much more complex execution plan, yet SQL Server used a full clustered index scan to read the data from the test fact table. SQL Server used 62,575 logical reads in this table.
The third test query is very selective—it selects only the rows for customer 378. If you remember, this customer has only 242 rows in the fact table. Let's call the third query the point query:
SELECT CustomerKey, Profit FROM dbo.FactTest WHERE CustomerKey = 378; SET STATISTICS IO OFF;
The query again did a full clustered index scan, and there were 63,601 logical reads.