Nonclustered and covering indexes

The BuildMart call centre looks up sales by customer all day. The clustered index is on SaleId, so every one of those lookups scans the table. This lesson adds indexes for searches like that. Start from a fresh dbo.Sales:

1DROP TABLE IF EXISTS dbo.Sales;2CREATE TABLE dbo.Sales (3    SaleId     int           NOT NULL CONSTRAINT PK_Sales PRIMARY KEY,4    SaleDate   date          NOT NULL,5    CustomerId int           NOT NULL,6    ProductId  int           NOT NULL,7    Quantity   int           NOT NULL,8    Amount     decimal(10,2) NOT NULL,9    Channel    varchar(10)   NOT NULL,10    Notes      char(100)     NULL11);12INSERT INTO dbo.Sales (SaleId, SaleDate, CustomerId, ProductId, Quantity, Amount, Channel)13SELECT value,14       DATEADD(day, value * 37 % 1096, '2022-01-01'),15       value * 7919 % 5000 + 1,16       value % 12 + 1,17       value % 7 + 1,18       value * 31 % 9000 / 100.0 + 1,19       CASE value % 10 WHEN 0 THEN 'Phone' WHEN 1 THEN 'Shop' WHEN 2 THEN 'Shop' ELSE 'Web' END20FROM GENERATE_SERIES(1, 100000);

Here is the call centre’s question for customer 3000: how many sales, how much, and the last sale date.

1SET STATISTICS IO ON;2SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Spent, MAX(SaleDate) AS LastSale3FROM dbo.Sales4WHERE CustomerId = 3000;5SET STATISTICS IO OFF;

SalesRows    Spent  LastSale
---------  -------  ----------
       20  1000.20  2024-12-21

Table 'Sales'. Scan count 1, logical reads 1794, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Twenty rows, and every page read. Add a nonclustered index on CustomerId:

1CREATE INDEX IX_Sales_CustomerId ON dbo.Sales (CustomerId);2 3SET STATISTICS IO ON;4SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Spent, MAX(SaleDate) AS LastSale5FROM dbo.Sales6WHERE CustomerId = 3000;7SET STATISTICS IO OFF;

SalesRows    Spent  LastSale
---------  -------  ----------
       20  1000.20  2024-12-21

Table 'Sales'. Scan count 1, logical reads 62, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

From a full scan to a few dozen reads. CREATE INDEX makes a nonclustered index unless you say otherwise.

What a nonclustered index holds

A nonclustered index is a separate B-tree. Its leaf rows hold the index key (CustomerId) and a row locator that leads back to the full row. On a clustered table, the locator is the clustering key, SaleId. (On a heap it is the row’s physical address, the RID.) The leaf rows are short, so the index is much smaller than the table:

1SELECT i.name AS IndexName, s.index_level, s.page_count2FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Sales'), NULL, NULL, 'DETAILED') AS s3JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id4ORDER BY i.index_id, s.index_level;

IndexName            index_level  page_count
-------------------  -----------  ----------
PK_Sales                       0        1786
PK_Sales                       1           6
PK_Sales                       2           1
IX_Sales_CustomerId            0         174
IX_Sales_CustomerId            1           1

So the query works in two steps. It seeks the small index to find the 20 SaleIds for customer 3000. The index has no Amount or SaleDate, so for each of the 20 rows it then seeks the clustered index by SaleId to fetch them. That second step is a key lookup. The plan shows both:

1SET SHOWPLAN_TEXT ON;2GO3SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Spent, MAX(SaleDate) AS LastSale4FROM dbo.Sales5WHERE CustomerId = 3000;6GO7SET SHOWPLAN_TEXT OFF;

StmtText
---------------------------------------------------------------------------------------------------------------------
SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Spent, MAX(SaleDate) AS LastSale
FROM dbo.Sales
WHERE CustomerId = 3000;

StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1002]=CONVERT_IMPLICIT(int,[Expr1013],0), [Expr1003]=CASE WHEN [Expr1013]=(0) THEN NULL ELSE [Expr1014] END))
       |--Stream Aggregate(DEFINE:([Expr1013]=Count(*), [Expr1014]=SUM([BuildMart].[dbo].[Sales].[Amount]), [Expr1004]=MAX([BuildMart].[dbo].[Sales].[SaleDate])))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([BuildMart].[dbo].[Sales].[SaleId]))
                 |--Index Seek(OBJECT:([BuildMart].[dbo].[Sales].[IX_Sales_CustomerId]), SEEK:([BuildMart].[dbo].[Sales].[CustomerId]=(3000)) ORDERED FORWARD)
                 |--Clustered Index Seek(OBJECT:([BuildMart].[dbo].[Sales].[PK_Sales]), SEEK:([BuildMart].[dbo].[Sales].[SaleId]=[BuildMart].[dbo].[Sales].[SaleId]) LOOKUP ORDERED FORWARD)

Read the plan from the innermost lines: an Index Seek on IX_Sales_CustomerId, then a Nested Loops join that, for each row found, runs a Clustered Index Seek marked LOOKUP on PK_Sales. Each lookup is a trip down the clustered index: about 3 reads per row, which explains most of the reads above.

Covering indexes: INCLUDE

If the index itself holds every column the query needs, there are no lookups: the index covers the query. Columns that are only needed in the result, not for searching, go in an INCLUDE list. They are stored in the leaf rows but are not part of the key.

1CREATE INDEX IX_Sales_CustomerId ON dbo.Sales (CustomerId)2    INCLUDE (SaleDate, Amount)3    WITH (DROP_EXISTING = ON);4 5SET STATISTICS IO ON;6SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Spent, MAX(SaleDate) AS LastSale7FROM dbo.Sales8WHERE CustomerId = 3000;9SET STATISTICS IO OFF;

SalesRows    Spent  LastSale
---------  -------  ----------
       20  1000.20  2024-12-21

Table 'Sales'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

DROP_EXISTING = ON replaces the index in one step. Now the 20 rows sit next to each other in the index leaf, with everything the query needs, so it costs the trip down and nothing else.

The tipping point

Lookups are cheap for a few rows, but each costs about three random reads. A scan of the whole table costs about 1,800 reads, however many rows match. So once a query returns more than a few hundred rows, a seek with lookups would cost more than a scan, and the optimizer stops using the index. The widget uses an index on CustomerId alone, so every row found needs a lookup:

SELECT * FROM dbo.Sales
WHERE CustomerId <= 4;   -- 80 rows

The point where the plan switches is the tipping point: here between 500 and 600 rows, about half a percent of the table. A non-covering index is only useful for queries that are very selective. When a useful query returns many rows, make the index cover it instead.

Composite indexes: column order matters

An index can have several key columns. Rows are sorted by the first column, then by the second within each value of the first, like a phone book sorted by surname, then first name. That order decides which searches can seek:

1CREATE INDEX IX_Sales_Product_Date ON dbo.Sales (ProductId, SaleDate);2 3SET STATISTICS IO ON;4-- 1. both columns: seek straight to product 4, June 20235SELECT COUNT(*) AS SalesRows FROM dbo.Sales6WHERE ProductId = 4 AND SaleDate >= '2023-06-01' AND SaleDate < '2023-07-01';7 8-- 2. the first column alone: seek to product 4, read all its dates9SELECT COUNT(*) AS SalesRows FROM dbo.Sales10WHERE ProductId = 4;11 12-- 3. the second column alone: June 2023 is spread through all 12 products13SELECT COUNT(*) AS SalesRows FROM dbo.Sales14WHERE SaleDate >= '2023-06-01' AND SaleDate < '2023-07-01';15SET STATISTICS IO OFF;

SalesRows
---------
      213

Table 'Sales'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SalesRows
---------
     8334

Table 'Sales'. Scan count 1, logical reads 21, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SalesRows
---------
     2737

Table 'Sales'. Scan count 1, logical reads 213, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Queries 1 and 2 seek, because they give the leading column. Query 3 does not: finding a date in an index sorted by product first is like finding everyone called “Ben” in a phone book. SQL Server reads the whole index instead (still cheaper than the table, because the index is narrow). The usual rule: put columns tested with = first, then the column tested with a range.

Unique and filtered indexes

A unique index also enforces a rule: no two rows may have the same key. A filtered index has a WHERE and holds only the rows that match it. Together they solve a common problem: emails must be unique, but many customers have none.

1CREATE UNIQUE INDEX UX_Customers_Email ON dbo.Customers (Email)2    WHERE Email IS NOT NULL;3 4INSERT INTO dbo.Customers (FirstName, LastName, City, Email, JoinedOn)5VALUES (N'Hana', N'Ito', N'Leeds', NULL, '2024-03-01');      -- another NULL: fine6 7INSERT INTO dbo.Customers (FirstName, LastName, City, Email, JoinedOn)8VALUES (N'Ian', N'Ross', N'York', N'asha@example.com', '2024-03-02');

Msg 2601, Level 14, State 1, Line 7
Cannot insert duplicate key row in object 'dbo.Customers' with unique index 'UX_Customers_Email'. The duplicate key value is (asha@example.com).
The statement has been terminated.

A plain UNIQUE constraint would allow only one NULL. The filtered index ignores the rows without an email, so it only checks the real addresses. Filtered indexes are also handy for small, hot subsets, such as WHERE Status = 'Pending'.

What indexes cost

Every index is a copy of some of the table’s data that SQL Server must keep correct. Each INSERT and DELETE changes every index on the table, and an UPDATE changes every index that contains a changed column. Indexes also take disk space and memory, and give the optimizer more plans to consider.

So index for the queries you actually run. This query shows how each index has been used since SQL Server started (the numbers depend on your workload):

1SELECT i.name AS IndexName, u.user_seeks, u.user_scans, u.user_lookups, u.user_updates2FROM sys.indexes AS i3LEFT JOIN sys.dm_db_index_usage_stats AS u4       ON u.object_id = i.object_id AND u.index_id = i.index_id AND u.database_id = DB_ID()5WHERE i.object_id = OBJECT_ID(N'dbo.Sales');

An index with many user_updates and no seeks, scans or lookups is paid for on every write and never used. It is a candidate for dropping.

1CREATE INDEX IX_Sales_Channel_Date ON dbo.Sales (Channel, SaleDate)2    INCLUDE (Amount);3 4SET STATISTICS IO ON;5SELECT COUNT(*) AS SalesRows, SUM(Amount) AS Total6FROM dbo.Sales7WHERE Channel = 'Phone' AND SaleDate >= '2024-06-01' AND SaleDate < '2024-07-01';8SET STATISTICS IO OFF;

SalesRows     Total
---------  --------
      274  12684.40

Table 'Sales'. Scan count 1, logical reads 5, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.