SQL Server Indexes Explained: Clustered, Non-Clustered and Covering Indexes

Indexes are the single most effective tool for making a database fast. A query that scans ten million rows in four seconds can return in four milliseconds with the right index. But indexes aren't free: every index slows down writes and uses storage. This guide explains how SQL Server indexes work, how to design them and how to verify they're actually used.

The phone-book analogy

Imagine a phone book sorted by last name. Finding "Mahmoudi" is fast: you jump to the M section. Finding everyone who lives on a particular street is slow: you must read every page. An index is exactly that — a sorted structure that lets the database jump straight to the data instead of reading everything.

How indexes are stored: B-trees

SQL Server stores indexes as B+ trees:

• The root and intermediate levels contain key ranges that point to lower levels.
• The leaf level contains the actual index entries, sorted by the key.

Finding a row means walking from root to leaf — typically 3 or 4 page reads even for millions of rows. That operation is an index seek. Reading the entire leaf level is an index scan.

Clustered indexes

A clustered index is the table: its leaf level contains the full rows, physically ordered by the clustered key. A table can therefore have only one clustered index.

CREATE TABLE Orders
(
    Id          INT IDENTITY PRIMARY KEY,   -- clustered by default
    CustomerId  INT           NOT NULL,
    Status      TINYINT       NOT NULL,
    Total       DECIMAL(18,2) NOT NULL,
    CreatedAt   DATETIME2     NOT NULL
);

A good clustered key is:

• Narrow — it's copied into every non-clustered index.
• Unique — otherwise SQL Server adds a hidden "uniquifier".
• Static — changing it moves the row.
• Ever-increasing — INT IDENTITY or sequential GUIDs avoid page splits on insert.

Warning: Random NEWID() GUIDs as clustered keys cause heavy fragmentation and page splits. If you need GUIDs, use NEWSEQUENTIALID() or keep the GUID non-clustered and cluster on an integer.

Non-clustered indexes

A non-clustered index is a separate structure containing the index key plus a row locator (the clustered key). You can have many per table.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
    ON Orders (CustomerId);

SELECT Id, Total, CreatedAt
FROM Orders
WHERE CustomerId = 42;

SQL Server seeks the index to find matching Ids, then performs a key lookup into the clustered index for Total and CreatedAt. For a few rows that's fine; for thousands of rows the lookups get expensive, and the optimizer may decide a full scan is cheaper.

Covering indexes with INCLUDE

A covering index contains every column the query needs, so no lookup is required:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
    ON Orders (CustomerId)
    INCLUDE (Total, CreatedAt);

INCLUDE columns are stored only at the leaf level; they don't affect the sort order and keep the upper levels small. Use them for columns that appear in SELECT but not in WHERE, JOIN or ORDER BY.

Composite indexes and column order

For multi-column indexes, order matters. An index on (CustomerId, CreatedAt) is sorted by CustomerId first, then by CreatedAt within each customer:

CREATE INDEX IX_Orders_Customer_Created ON Orders (CustomerId, CreatedAt DESC);

It efficiently supports:

WHERE CustomerId = 42                                 -- ✅ seek
WHERE CustomerId = 42 AND CreatedAt >= '2026-01-01'   -- ✅ seek on both
WHERE CustomerId = 42 ORDER BY CreatedAt DESC         -- ✅ seek, no sort

But not:

WHERE CreatedAt >= '2026-01-01'                        -- ❌ leading column missing → scan

Rules of thumb:

1. Put equality columns first, range columns (>, BETWEEN, LIKE 'abc%') last.
2. Put the most frequently filtered columns first.
3. Match the index order to common ORDER BY clauses to avoid sort operations.

Filtered indexes

When queries target a small subset of rows, index only that subset:

CREATE INDEX IX_Orders_Pending
    ON Orders (CreatedAt)
    WHERE Status = 0;   -- only pending orders

Filtered indexes are small, cheap to maintain and very fast — ideal for "active", "unread" or "not deleted" flags.

Queries that can't use an index (non-SARGable)

An expression is SARGable (Search ARGument-able) when SQL Server can seek an index with it. Wrapping a column in a function usually breaks this:

Implicit conversions are a hidden version of the same problem: comparing an NVARCHAR parameter with a VARCHAR column, for example, can force a scan.

Reading execution plans

In SQL Server Management Studio, press Ctrl+M (Include Actual Execution Plan) and run your query. Look for:

• Index Seek — good: targeted access.
• Index/Clustered Index Scan — reads everything; fine for small tables, suspicious for large ones.
• Key Lookup — consider a covering index if it runs many times.
• Sort with high cost — an index matching the ORDER BY can remove it.
• Missing Index suggestions (green text) — useful hints, but evaluate them; they don't consider your whole workload.

You can also measure I/O:

SET STATISTICS IO, TIME ON;
SELECT ... ;   -- compare "logical reads" before and after adding an index

Indexes in EF Core

Define indexes in your model so migrations create them:

modelBuilder.Entity<Order>(e =>
{
    e.HasIndex(o => new { o.CustomerId, o.CreatedAt })
     .IsDescending(false, true)
     .IncludeProperties(o => new { o.Total });   // SQL Server provider

    e.HasIndex(o => o.CreatedAt)
     .HasFilter("[Status] = 0")
     .HasDatabaseName("IX_Orders_Pending");

    e.HasIndex(o => o.Email).IsUnique();
});

Remember that foreign keys are not automatically indexed by SQL Server, although EF Core creates indexes for them by convention. If you build schemas by hand, index your foreign keys — joins and cascading deletes depend on them.

The cost of indexes

Every INSERT, UPDATE and DELETE must update all affected indexes. Too many indexes mean:

• slower writes and more locking;
• more storage and memory pressure;
• longer maintenance windows.

Periodically review usage and remove indexes that are never read:

SELECT OBJECT_NAME(s.object_id) AS TableName, i.name AS IndexName,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes 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;

An index with many updates and zero seeks is pure overhead.

Summary

• The clustered index is the table itself; choose a narrow, unique, static, increasing key.
• Non-clustered indexes speed up filters and joins; INCLUDE makes them covering.
• Column order in composite indexes is critical: equality first, range last.
• Write SARGable predicates and avoid functions on indexed columns.
• Verify with execution plans and STATISTICS IO, and remove unused indexes.

FAQ

How many indexes should a table have?

There's no magic number. OLTP tables with heavy writes usually do well with a handful of well-chosen indexes; read-heavy reporting tables can afford more.

Should I index every column used in WHERE?

No. Index columns used in frequent, important queries, and prefer a few composite/covering indexes over many single-column ones.

Do I need to rebuild indexes?

Fragmentation matters less on modern SSD storage, but keeping statistics up to date matters a lot. Use a maintenance solution that updates statistics regularly and rebuilds heavily fragmented indexes.