Composite and Covering Indexes in SQL Server

• COMPOSITE index: index with MORE THAN ONE key column, e.g. (CustomerID, OrderDate). Sorted by the 1st column, then by the 2nd inside it, ... (like a phone book: LastName, FirstName).

• LEFT-PREFIX RULE: an index on (A, B, C) can SEEK on A | A, B | A, B, C but NOT on B alone or C alone (it would scan).

COLUMN ORDER    : put EQUALITY columns first (=), then RANGE / inequality columns (>, BETWEEN).

COVERING index  : contains EVERY column a query needs -> no Key Lookup.
                  Extra columns can be added with INCLUDE (stored only at leaf level,
                  not part of the sort order).

The demo data on this site is deliberately small (300 orders and 50 customers) so the script runs instantly. With so few rows, SQL Server may choose a scan where a big table would use a seek, and the logical reads are tiny. To see the real performance difference, change TOP (300) to TOP (300000) and TOP (50) to TOP (50000) in the demo data script.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Lesson table

DROP TABLE IF EXISTS dbo.CompOrders;
SELECT * INTO dbo.CompOrders FROM dbo.BigOrdersData;
ALTER TABLE dbo.CompOrders ADD CONSTRAINT PK_CompOrders PRIMARY KEY CLUSTERED (OrderID);
GO

SET STATISTICS IO ON;
GO

(300 rows affected)

Part A: Single-column vs Composite Index

Query: orders of one customer in 2025

Baseline (no index) -> clustered index scan

SELECT OrderID, OrderDate FROM dbo.CompOrders
WHERE CustomerID = 25 AND OrderDate >= '2025-01-01';

Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Single-column index on CustomerID: seek CustomerID, then Filter on OrderDate

CREATE INDEX IX_Comp_CustomerID ON dbo.CompOrders (CustomerID);
SELECT OrderID, OrderDate FROM dbo.CompOrders
WHERE CustomerID = 25 AND OrderDate >= '2025-01-01';

Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Composite index (CustomerID, OrderDate): seeks directly to the right range, no lookup

CREATE INDEX IX_Comp_CustomerID_OrderDate ON dbo.CompOrders (CustomerID, OrderDate);
SELECT OrderID, OrderDate FROM dbo.CompOrders
WHERE CustomerID = 25 AND OrderDate >= '2025-01-01';
GO

Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Table 'CompOrders'. 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.

Part B: Left-prefix Rule

Leading column used -> Seek

SELECT OrderID FROM dbo.CompOrders WHERE CustomerID = 25;

Table 'CompOrders'. 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.

Only the 2nd column -> cannot seek, index Scan (or clustered scan)

SELECT OrderID FROM dbo.CompOrders WHERE OrderDate = '2025-03-15';

Table 'CompOrders'. 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.

Both columns -> Seek on both

SELECT OrderID FROM dbo.CompOrders WHERE CustomerID = 25 AND OrderDate = '2025-03-15';
GO

Table 'CompOrders'. 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.

Part C: Column Order - Equality First, Range Second

Query: pending orders in a date range

Range column first (OrderDate, Status): must read All statuses in the date range

CREATE INDEX IX_Comp_Date_Status ON dbo.CompOrders (OrderDate, Status);
GO
SELECT OrderID FROM dbo.CompOrders WITH (INDEX (IX_Comp_Date_Status))
WHERE Status = 'Pending' AND OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Table 'CompOrders'. 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.

Equality column first (Status, OrderDate): jumps straight to Pending + date range

CREATE INDEX IX_Comp_Status_Date ON dbo.CompOrders (Status, OrderDate);
GO
SELECT OrderID FROM dbo.CompOrders WITH (INDEX (IX_Comp_Status_Date))
WHERE Status = 'Pending' AND OrderDate BETWEEN '2024-01-01' AND '2024-12-31';
-- Compare the logical reads of C1 and C2 in the Messages tab.
GO

Table 'CompOrders'. 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.

Table 'CompOrders'. 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.

Part D: Covering Index with INCLUDE

Query: a customer's orders with amount and status

Index (CustomerID, OrderDate) does not contain Amount and Status -> Key Lookup per row

SELECT OrderID, OrderDate, Amount, Status
FROM dbo.CompOrders
WHERE CustomerID = 25
ORDER BY OrderDate DESC;

Table 'CompOrders'. Scan count 1, logical reads 14, 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.

Covering index: key = search/sort columns, INCLUDE = columns only displayed

CREATE INDEX IX_Comp_Customer_Covering
    ON dbo.CompOrders (CustomerID, OrderDate DESC)
    INCLUDE (Amount, Status);

SELECT OrderID, OrderDate, Amount, Status
FROM dbo.CompOrders
WHERE CustomerID = 25
ORDER BY OrderDate DESC;          -- Index Seek only : no lookup, no sort
GO

Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Table 'CompOrders'. 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.

Covering index for an aggregate report (Region + Status totals)

SELECT Region, Status, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM dbo.CompOrders
GROUP BY Region, Status;          -- clustered scan of the wide table

CREATE INDEX IX_Comp_Region_Status_Amt ON dbo.CompOrders (Region, Status) INCLUDE (Amount);

SELECT Region, Status, COUNT(*) AS Orders, SUM(Amount) AS Revenue
FROM dbo.CompOrders
GROUP BY Region, Status;          -- scans the much narrower NC index (fewer reads), no sort needed
GO

Table 'Worktable'. Scan count 0, logical reads 0, 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.
Table 'CompOrders'. Scan count 1, logical reads 8, 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.
Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Table 'CompOrders'. 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.

Part E: Index Supports ORDER BY / TOP (avoid Sort operator)

-- Latest 10 orders of each product... single product example
SELECT TOP (10) OrderID, OrderDate, Amount
FROM dbo.CompOrders
WHERE ProductID = 7
ORDER BY OrderDate DESC;          -- before index: scan + sort (TOP N sort)

CREATE INDEX IX_Comp_Product_Date ON dbo.CompOrders (ProductID, OrderDate DESC) INCLUDE (Amount);

SELECT TOP (10) OrderID, OrderDate, Amount
FROM dbo.CompOrders
WHERE ProductID = 7
ORDER BY OrderDate DESC;          -- after index: seek + read first 10 rows, done
GO

SET STATISTICS IO OFF;
GO

Table 'CompOrders'. Scan count 1, logical reads 8, 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.
Table 'CompOrders'. Scan count 1, logical reads 8, 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.

Table 'CompOrders'. 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.

Part F: Key vs INCLUDE - which columns go where?

Limits : 16 key columns, 900 bytes (clustered) / 1700 bytes (nonclustered) key size. INCLUDE columns don't count toward the key size limit.

-- Redundant indexes : IX_Comp_CustomerID is a left-prefix of IX_Comp_CustomerID_OrderDate,
-- so it is no longer needed. Remove it.
DROP INDEX IX_Comp_CustomerID ON dbo.CompOrders;

-- Show all indexes we built
SELECT i.name AS IndexName,
       STRING_AGG(CASE WHEN ic.is_included_column = 0 THEN c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END END, ', ')
           WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ') AS IncludedColumns
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns c        ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID('dbo.CompOrders')
GROUP BY i.name;
GO

Practice Questions

1. Write the best covering index for:
2. For WHERE ProductID = 3 AND Region = 'East' AND OrderDate > '2025-01-01' which column order is best? Test it.
3. Join dbo.CompOrders to dbo.BigCustomersData on CustomerID filtered by City = 'Pune'.
4. Find which of the indexes above are redundant (left-prefix of another) and drop them.