LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 3020 min read

Indexes

Understand clustered and non-clustered indexes in SQL Server, how to create them, and when they help - or hurt - query performance.

Introduction

Ask SQL Server to find one row in a table with 20 million rows, and without help it has to scan every single one. An index changes that by giving the engine a fast path straight to the rows it needs. Indexes are arguably the single biggest lever for query performance in SQL Server, and understanding the difference between a clustered and a non-clustered index is core knowledge for anyone writing or tuning T-SQL.

What You Will Learn
  • What an index is and how it speeds up lookups.
  • The difference between clustered and non-clustered indexes.
  • How to create indexes with CREATE INDEX.
  • Composite indexes and the INCLUDE clause.
  • When adding an index helps - and when it actively hurts.

What Is an Index?

An index is a separate on-disk structure, built from one or more columns of a table, that SQL Server keeps sorted so it can locate rows without reading the whole table. It works much like the index at the back of a textbook: instead of reading every page to find a topic, you look it up alphabetically and jump straight to the right page. The tradeoff is that indexes take extra storage space and must be updated whenever the underlying data changes, which adds a small cost to every INSERT, UPDATE, and DELETE.

Clustered Indexes

A clustered index determines the physical order in which rows are stored on disk. Because the data itself is sorted by the index key, a table can have at most one clustered index. Most tables have a clustered index on their primary key, since SQL Server automatically creates one when you define a PRIMARY KEY constraint (unless you specify otherwise).

CREATE TABLE dbo.Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,  -- creates a clustered index by default
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    HireDate   DATE NOT NULL,
    DepartmentID INT NOT NULL
);
Table = Clustered Index

You can think of a table with a clustered index as the index - the leaf level of a clustered index IS the actual data rows, sorted by the key. A table with no clustered index at all is called a heap.

Non-Clustered Indexes

A non-clustered index is a separate structure from the table data. It stores a sorted copy of the indexed column(s) plus a pointer back to the full row (the clustering key, if the table has one). A table can have many non-clustered indexes, each optimized for a different query pattern - for example, one for looking up employees by last name, and another for looking up employees by department.

CREATE NONCLUSTERED INDEX IX_Employees_LastName
ON dbo.Employees (LastName);

Creating Indexes

The general syntax for CREATE INDEX is the same shape whether the index is clustered or non-clustered: name it, say what it goes on, and list the columns.

-- Speeds up: SELECT * FROM Employees WHERE DepartmentID = 4;
CREATE NONCLUSTERED INDEX IX_Employees_DepartmentID
ON dbo.Employees (DepartmentID);


-- Confirm it exists
SELECT name, type_desc, is_unique
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.Employees');
Result

Click Run to see what this code prints.

Composite and Included Columns

An index on more than one column is called a composite index. Column order matters: an index on (DepartmentID, LastName) helps queries that filter on DepartmentID alone, or on DepartmentID and LastName together, but does not help a query that filters on LastName alone. The INCLUDE clause lets you add extra columns to the leaf level of a non-clustered index (without making them part of the sort key) so a query can be satisfied entirely from the index, avoiding an extra lookup back to the table - this is called a covering index.

CREATE NONCLUSTERED INDEX IX_Employees_Dept_Hire
ON dbo.Employees (DepartmentID, HireDate)
INCLUDE (FirstName, LastName);


-- This query can be fully answered from the index above (a "covering" index)
SELECT FirstName, LastName, HireDate
FROM dbo.Employees
WHERE DepartmentID = 4
ORDER BY HireDate;

When Indexes Help

  • Columns frequently used in WHERE clauses to filter rows.
  • Columns used in JOIN conditions between large tables.
  • Columns used in ORDER BY, since a matching index can avoid an expensive sort.
  • Foreign key columns, which are not indexed automatically in SQL Server and are often joined on.

When Indexes Hurt

Every index has to be maintained. Each INSERT, UPDATE, or DELETE that touches an indexed column must also update every index that includes it, which adds write overhead and storage. A table with ten rarely-queried indexes on a heavily-written table can slow down writes noticeably for little or no read benefit.

Diminishing (and Negative) Returns

Adding an index is not free. Low-selectivity columns (like a Gender or IsActive flag with only two or three distinct values) rarely benefit from their own index, because the optimizer often decides a full scan is cheaper than following an index with poor selectivity anyway.

Common Mistakes

Avoid These Mistakes
  • Indexing every column 'just in case,' which bloats storage and slows down writes.
  • Putting the least selective column first in a composite index, wasting its usefulness.
  • Forgetting that foreign key columns are not indexed automatically - add them explicitly if you join on them often.
  • Creating a new index without checking whether an existing one already covers the same query pattern.
  • Never revisiting indexes after the application evolves - unused indexes should eventually be dropped.

Best Practices

  • Index based on actual query patterns (WHERE, JOIN, ORDER BY columns), not guesswork.
  • Put the most selective, most frequently filtered column first in a composite index.
  • Use INCLUDE to create covering indexes for your most performance-critical queries.
  • Periodically review sys.dm_db_index_usage_stats to find unused indexes and remove them.
  • Keep the number of indexes on write-heavy tables intentionally lean.

Frequently Asked Questions

No, a table can have at most one clustered index, because the data itself can only be physically sorted one way.

A heap is a table with no clustered index. Rows are stored in no particular order, which can make lookups by non-indexed columns slower.

By default, yes, but you can specify CREATE TABLE ... PRIMARY KEY NONCLUSTERED to keep the primary key as a unique constraint while putting the clustered index elsewhere.

Query the sys.dm_db_index_usage_stats dynamic management view, which tracks seeks, scans, lookups, and updates per index since the last SQL Server restart.

Key Takeaways

  • An index lets SQL Server find rows without scanning the whole table.
  • A clustered index defines the physical row order; a table can have only one.
  • A non-clustered index is a separate, smaller structure and a table can have many.
  • Composite indexes are order-sensitive; INCLUDE adds columns without widening the sort key.
  • Indexes speed up reads but add overhead to writes - they are a tradeoff, not a free upgrade.

Summary

Indexes are how SQL Server avoids scanning entire tables for every query. Understanding clustered versus non-clustered indexes, and when an index actually pays for itself, is essential for writing performant T-SQL. Next, you will move from data retrieval into encapsulating logic on the server with stored procedures.

Next Lesson →

Stored Procedures