Clustered vs. nonclustered indexes in SQL Server

Covering indexes and INCLUDE

If a nonclustered index contains every column a query needs, SQL Server never touches the base table. That's a covering index. Use INCLUDE to add columns to the leaf level without making them part of the key:

CREATE NONCLUSTERED INDEX IX_Customers_Country
ON dbo.Customers (Country)
INCLUDE (Email, CreatedAt);

-- Fully covered: no key lookups
SELECT Email, CreatedAt FROM dbo.Customers WHERE Country = 'IN';

Choosing a good clustered key

Because every nonclustered index carries the clustered key, a good clustered key is:

• Narrow — an int or bigint beats a wide composite key.
• Unique — otherwise SQL Server adds a hidden 4-byte uniquifier to duplicates.
• Static — changing it means moving the row and updating every nonclustered index.
• Ever-increasing — new rows append at the end instead of splitting pages in the middle.

An IDENTITY column ticks every box. A random uniqueidentifier from NEWID() does not; if you need GUIDs, consider NEWSEQUENTIALID() as a default.

Seeing the difference

Turn on I/O statistics and compare:

SET STATISTICS IO ON;

SELECT * FROM dbo.Customers WHERE Email = 'ada@example.com';

Without IX_Customers_Email, you'll see a clustered index scan reading every page. With it, you'll see a seek reading just a handful of pages.

Don't over-index

Every index speeds up some reads and slows down every insert, update and delete that touches its columns. Review usage periodically:

SELECT OBJECT_NAME(s.object_id) AS table_name,
       i.name AS index_name,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
ORDER BY s.user_updates DESC;

Indexes with many updates and almost no seeks or scans are candidates for removal (these counters reset when the instance restarts, so judge over a representative period).

Key takeaways

1. The clustered index is the table, sorted by its key — one per table.
2. Nonclustered indexes point back to rows via the clustered key.
3. Choose a narrow, unique, static, ever-increasing clustered key.
4. Use INCLUDE to build covering indexes for hot queries — but don't over-index.

What causes slow queries in SQL Server, and how do I fix them?

A diagnostic catalogue of slow query causes in SQL Server: how to tell running from waiting, the plan or wait signature of each cause, and the fix.

#execution-plans#performance#troubleshooting