Index Design Rules: Ten Don’ts and Where They Break

These index design rules came from a client’s list of ten index don’ts, and readers argued with several of them. Some rules can be checked with a query. Others are warnings that break in the right workload. This post tests the checkable ones on a demo database and says which rules are alarms and which are laws.

The Client’s Index Design Rules

A fintech client built the list with me during health checks. It was short enough to pin above a developer’s desk. This table keeps the ten rules and adds the question each one raises.

Treat the numbers in these index design rules as alarms. A table with nine indexes isn’t wrong. It is a table that needs someone to explain why. The demo below builds four tables that break rules on purpose, and queries show each break.

Build the Demo

The first script creates the demo database and four tables. Orders has ten indexes, one of them wide. Customers has a wide key and a clustering key made of two columns. AuditLog is a heap. Shipments has a foreign key with no index. The second script loads 20,000 rows into Orders and into Customers.

IF DB_ID(N'IndexRulesDemo') IS NULL CREATE DATABASE IndexRulesDemo;
GO
USE IndexRulesDemo;
GO
DROP TABLE IF EXISTS dbo.Shipments;
DROP TABLE IF EXISTS dbo.Orders;
DROP TABLE IF EXISTS dbo.AuditLog;
DROP TABLE IF EXISTS dbo.Customers;
CREATE TABLE dbo.AuditLog (EntryID int NOT NULL, Note nvarchar(60) NOT NULL);
CREATE TABLE dbo.Orders (
    OrderID int NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerID int NOT NULL, ProductID int NOT NULL, Status tinyint NOT NULL, Region tinyint NOT NULL,
    Channel tinyint NOT NULL, Priority tinyint NOT NULL, CreatedOn date NOT NULL, Amount decimal(10,2) NOT NULL);
CREATE TABLE dbo.Shipments (
    ShipmentID int NOT NULL CONSTRAINT PK_Shipments PRIMARY KEY,
    OrderID int NOT NULL CONSTRAINT FK_Shipments_Orders REFERENCES dbo.Orders (OrderID),
    ShippedOn date NOT NULL);
CREATE TABLE dbo.Customers (
    DepartmentID int NOT NULL, CustomerID int NOT NULL, CustomerName nvarchar(60) NOT NULL, City nvarchar(40) NOT NULL,
    Phone nvarchar(20) NOT NULL, Email nvarchar(60) NOT NULL, Segment tinyint NOT NULL, Region tinyint NOT NULL, Tier tinyint NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY (DepartmentID, CustomerID));
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);
CREATE INDEX IX_Orders_ProductID ON dbo.Orders (ProductID);
CREATE INDEX IX_Orders_Status ON dbo.Orders (Status);
CREATE INDEX IX_Orders_Region ON dbo.Orders (Region);
CREATE INDEX IX_Orders_Channel ON dbo.Orders (Channel);
CREATE INDEX IX_Orders_Priority ON dbo.Orders (Priority);
CREATE INDEX IX_Orders_CreatedOn ON dbo.Orders (CreatedOn);
CREATE INDEX IX_Orders_Amount ON dbo.Orders (Amount);
CREATE INDEX IX_Orders_Covering ON dbo.Orders (CustomerID, CreatedOn) INCLUDE (ProductID, Status, Region, Channel, Priority, Amount);
CREATE INDEX IX_Customers_Wide ON dbo.Customers (CustomerName, City, Phone, Email, Segment, Region) INCLUDE (Tier);
CREATE INDEX IX_Customers_Name ON dbo.Customers (CustomerName);
CREATE INDEX IX_Customers_NameExplicit ON dbo.Customers (CustomerName, DepartmentID, CustomerID);
CREATE INDEX IX_Customers_DeptName ON dbo.Customers (DepartmentID, CustomerName);
GO
INSERT INTO dbo.Orders (OrderID, CustomerID, ProductID, Status, Region, Channel, Priority, CreatedOn, Amount)
SELECT TOP (20000) n, n % 500, n % 200, n % 4, n % 5, n % 3, n % 3, DATEADD(DAY, n % 365, '2026-01-01'), n % 90 + 10
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x;
INSERT INTO dbo.Customers (DepartmentID, CustomerID, CustomerName, City, Phone, Email, Segment, Region, Tier)
SELECT TOP (20000) n % 50 + 1, n, CONCAT(N'Customer ', n), N'Austin', N'555-0100', CONCAT(N'c', n, N'@example.com'), n % 5, n % 4, n % 3
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
      FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b) AS x;

Rules 2, 3, 6 and 7: Audit the Counts

Four rules are counts: indexes per table, heaps, key columns and included columns. One query checks all four. The three thresholds sit at the top, so you can change them to the numbers your team accepts. The query counts the primary key as an index, as the rule does.

DECLARE @MaxIndexes int = 7, @MaxKeyColumns int = 5, @MaxIncluded int = 5;
WITH PerTable AS (
    SELECT s.name AS SchemaName, t.name AS TableName,
           COUNT(CASE WHEN i.index_id > 0 THEN 1 END) AS IndexCount,
           MAX(CASE WHEN i.type = 0 THEN 1 ELSE 0 END) AS IsHeap,
           MAX(c.KeyColumns) AS MaxKeyColumns,
           MAX(c.IncludedColumns) AS MaxIncludedColumns
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    INNER JOIN sys.indexes AS i ON i.object_id = t.object_id
    OUTER APPLY (SELECT SUM(CASE WHEN ic.is_included_column = 0 THEN 1 ELSE 0 END) AS KeyColumns,
                        SUM(CASE WHEN ic.is_included_column = 1 THEN 1 ELSE 0 END) AS IncludedColumns
                 FROM sys.index_columns AS ic
                 WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id) AS c
    WHERE t.is_ms_shipped = 0
    GROUP BY s.name, t.name
)
SELECT SchemaName, TableName, IndexCount, MaxKeyColumns, MaxIncludedColumns,
       ISNULL(NULLIF(CONCAT_WS(N'; ',
           IIF(IsHeap = 1, N'Heap', NULL),
           IIF(IndexCount > @MaxIndexes, N'Too many indexes', NULL),
           IIF(MaxKeyColumns > @MaxKeyColumns, N'Wide key', NULL),
           IIF(MaxIncludedColumns > @MaxIncluded, N'Many included columns', NULL)), N''), N'OK') AS Flags
FROM PerTable
ORDER BY TableName;

A flag is a prompt for a conversation. AuditLog is a heap. A heap suits a staging or log table that is only appended to and read in full. It hurts when you look rows up by a key. Customers has a six column key. That is fine when queries filter on all six, and a waste when they filter on one.

Rule 4: Foreign Keys

The rule says not to index every foreign key. A reader objected that an index on a foreign key helps many queries, and the reply changed the wording. Both are right. An index on a foreign key column helps joins to the parent. It also helps deletes in the parent, because SQL Server must check the child table for each deleted row. Without an index that check scans the child.

So list the foreign keys that have no index on their first column. Then decide for each one with the workload in front of you.

SELECT OBJECT_NAME(fk.parent_object_id) AS TableName, fk.name AS ForeignKey, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS FirstColumn
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id AND fkc.constraint_column_id = 1
WHERE NOT EXISTS (SELECT 1 FROM sys.index_columns AS ic
                  WHERE ic.object_id = fk.parent_object_id AND ic.key_ordinal = 1 AND ic.column_id = fkc.parent_column_id)
ORDER BY TableName, ForeignKey;

Rules 1 and 2: What an Index Costs

A new index costs every insert. SQL Server writes each new row into every index of the table. After the load, every index of Orders shows 20,000 inserts.

SELECT i.name AS IndexName, os.leaf_insert_count AS LeafInserts
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(), i.object_id, i.index_id, NULL) AS os
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY i.name;

All ten rows show 20,000. An update is different. It changes only the indexes that hold a changed column, and a reader made that point against the rule. The next script updates Status on 1,000 orders and reports what each index recorded.

DECLARE @before TABLE (IndexName sysname, Ins bigint, Gh bigint, Upd bigint);
INSERT @before
SELECT i.name, os.leaf_insert_count, os.leaf_ghost_count, os.leaf_update_count
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(), i.object_id, i.index_id, NULL) AS os
WHERE i.object_id = OBJECT_ID(N'dbo.Orders');
UPDATE dbo.Orders SET Status = (Status + 1) % 4 WHERE OrderID <= 1000;
SELECT i.name AS IndexName, os.leaf_insert_count - b.Ins AS Inserted, os.leaf_ghost_count - b.Gh AS Ghosted, os.leaf_update_count - b.Upd AS Updated
FROM sys.indexes AS i
CROSS APPLY sys.dm_db_index_operational_stats(DB_ID(), i.object_id, i.index_id, NULL) AS os
INNER JOIN @before AS b ON b.IndexName = i.name
WHERE i.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY i.name;

Three indexes did work: the one on Status, the one that includes Status, and the clustered primary key. The other seven recorded nothing. So the cost of ten indexes depends on the write pattern. A table that is mostly updated in one column carries ten indexes cheaply. A table that takes bulk inserts pays for all ten every time. That is why the limit of seven works as an alarm and not as a law.

Rule 8: The Clustering Key

Every nonclustered index carries the clustering key already. Customers is clustered on DepartmentID and CustomerID. Three indexes use those columns in three ways. The first names only CustomerName. The second adds the clustering key columns explicitly, at the end. The third puts DepartmentID first.

SELECT i.name AS IndexName, ps.in_row_data_page_count AS Pages
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.Customers') AND i.name IN (N'IX_Customers_Name', N'IX_Customers_NameExplicit', N'IX_Customers_DeptName')
ORDER BY i.name;

All three take 112 pages. SQL Server doesn’t store the clustering key twice, so naming it at the end costs nothing. The rule is right that it adds nothing there. It is wrong when the column goes first. A key that starts with DepartmentID serves queries that filter on department, and the other two can’t seek on it. So the rule holds only for the tail position.

Rules 5, 9 and 10: Maintenance

A nightly rebuild costs log space and time. Rebuild when the data says so. Update statistics on a tighter schedule than you rebuild. Fill factor belongs on the index. The server default is 0, which means full pages. One lower value for every index wastes space on indexes that never split. Maintenance is also more than rebuilds. It includes dropping indexes nobody reads, which Unused Index Script: Find Indexes That Only Cost You Writes finds.

Where the Index Design Rules Break

You could argue that a limit of seven is arbitrary. It is. Readers pointed to tables with 80 columns and to ERP products that ship with their own indexes. Those tables need more, and the right number is whatever the workload proves.

One reader noted that a table with one clustered index can still be slow. Following rules never replaces workload analysis. The post on Reasons for Slow Performance in SQL Server: The Top Five covers that.

What to Remember

Treat index design rules as alarms, and use the audit query to raise them. Explain every flag, don’t only fix it. Count what each index costs your writes, and drop the ones that cost more than they serve. Run the cleanup when you finish the demo.

USE master;
GO
IF DB_ID(N'IndexRulesDemo') IS NOT NULL
BEGIN
    ALTER DATABASE IndexRulesDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE IndexRulesDemo;
END;

An index rule is not a law, it is a question you must be able to answer.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.