SQL Server Indexes – Clustered, Nonclustered and Composite Indexes
Quick Answer: An index helps SQL Server find records without checking every row. A clustered index defines how table data is organized. A nonclustered index stores a separate search structure. A composite index uses multiple columns together.
1. Why Do We Need SQL Server Indexes?
Imagine Lakshmi manages a library with 50,000 books. Visitors ask her to find books using ISBN, title, or author.
Every book has different details. Lakshmi needs to find the correct book quickly.
Would she check every book whenever someone asks for one?
The problem without indexes
Without a suitable index, SQL Server may scan every row. This is called a Table Scan for a heap or a Clustered Index Scan for a clustered table.
Example 1: Search library books without a secondary index
Assume the Books table contains 50,000 records and no index on ISBN.
SELECT BookId, Title, AuthorName
FROM Books
WHERE ISBN = '9780131103627';
GO
Expected result
BookId Title AuthorName ------ ----------------------- ----------- 101 The C Programming Book Dennis Ritchie
The query can return the correct book. However, SQL Server may examine many records.
- Searching can require reading many data pages.
- Large tables can increase the amount of reading.
- Repeated searches can consume additional database resources.
- Performance can become difficult to maintain as data grows.
Now imagine Lakshmi handling thousands of book searches daily. Checking every book becomes expensive.
We need a better search structure. We also need an efficient way to locate records using different columns.
The solution: SQL Server Indexes
Index = a data structure that helps SQL Server locate records.
Clustered index = an index whose leaf level contains the table's data rows.
Nonclustered index = a separate index structure containing key values and row locators.
Composite index = an index built using two or more columns.
SQL Server uses a balanced tree structure for traditional rowstore indexes. It follows index keys to find matching records.
Animation 1: How an index helps find a book
Follow Lakshmi as she searches using an ISBN index.
Lakshmi searches by ISBN. The index helps SQL Server locate the matching book record.
2. Implement SQL Server Indexes
Let's use Lakshmi's library database to understand each index type.
Index key = a column or group of columns used to organize an index.
Leaf level = the lowest level of an index tree.
Row locator = information that helps locate the actual table row.
Clustered Index
A clustered index stores the table's data rows at its leaf level. Its key defines their logical ordering.
SQL Server allows only one clustered index per table. The table's data cannot have two different clustered index arrangements.
For example, Lakshmi can use BookId as the clustered index key.
Nonclustered Index
A nonclustered index stores index keys separately from the table's data rows.
It also stores a row locator. SQL Server uses that locator to find the matching record.
A nonclustered index can help Lakshmi search books using ISBN or Title. A table can have multiple nonclustered indexes.
Composite Index
A composite index uses two or more columns as its key. The order of those columns matters.
Suppose Lakshmi searches books using AuthorName and PublishedYear. A composite index can support that search pattern.
An index on (AuthorName, PublishedYear) is ordered by AuthorName first. PublishedYear is the second key.
This index can support searches on AuthorName alone. It can also support searches using both columns together.
Important: SQL Server does not automatically use every available index. Its query optimizer chooses an execution plan based on the query, statistics and estimated cost.
Create clustered, nonclustered and composite indexes
Example 2: Create three types of indexes for library books
Assumes the Books table exists with the columns shown below.
CREATE CLUSTERED INDEX IX_Books_BookId
ON Books(BookId);
GO
CREATE NONCLUSTERED INDEX IX_Books_ISBN
ON Books(ISBN);
GO
CREATE NONCLUSTERED INDEX IX_Books_Author_Year
ON Books(AuthorName, PublishedYear);
GO
-- Search by author and publication year
SELECT BookId, Title, AuthorName, PublishedYear
FROM Books
WHERE AuthorName = 'Dennis Ritchie'
AND PublishedYear = 1978;
GO
Notice that IX_Books_BookId organizes the table's data using BookId.
IX_Books_ISBN creates a separate search structure. IX_Books_Author_Year uses two columns together.
The composite index matches Lakshmi's author and publication year search. SQL Server can consider it when choosing the execution plan.
3. Quick Summary
Clustered vs Nonclustered vs Composite Index
| Index Type | How It Works | Example |
|---|---|---|
| Clustered | Leaf level contains table data rows. | BookId |
| Nonclustered | Separate structure with key and row locator. | ISBN |
| Composite | Uses multiple columns as index keys. | AuthorName, PublishedYear |
The important terms to remember
- Index: Structure that helps locate database records.
- Clustered: Index whose leaf level contains table data.
- Nonclustered: Separate index structure with row locators.
- Composite: Index containing multiple key columns.
- Index Seek: Access method that navigates an index to find matching keys.
4. Common Mistakes and Best Practices
1. Creating too many indexes
Every additional index needs storage and maintenance. Data changes can also make index maintenance more expensive.
Better approach: Create indexes based on actual query patterns and workload.
2. Ignoring composite index column order
An index on AuthorName and PublishedYear is not equivalent to the reverse order.
Better approach: Choose key order based on common filters, joins and sorting requirements.
3. Assuming every query uses an index
SQL Server may choose a scan when it estimates that scanning costs less.
Better approach: Review actual execution plans and measure query performance.
5. How SQL Server Indexes Come Up in Interviews
Question 1: What is a clustered index?
What the interviewer expects: Explain its relationship with table data.
Strong answer: "A clustered index stores the table's data rows at its leaf level. SQL Server allows only one clustered index per table."
Question 2: What is the difference between clustered and nonclustered indexes?
What the interviewer expects: Explain their storage structures.
Strong answer: "A clustered index contains the table data at its leaf level. A nonclustered index has a separate structure with key values and row locators."
Question 3: What is a composite index?
What the interviewer expects: Explain multiple index key columns.
Strong answer: "A composite index uses multiple columns together. Their order matters because the index is organized by the first key, followed by the remaining keys."
6. Hands-On Practice
Beginner: School database
Create a nonclustered index on StudentName in a Students table.
Expected result: Write a query that searches students by name.
Intermediate: Banking database
Create a composite index using BranchId and AccountNumber. Write queries using both columns and BranchId alone.
Hint: Check how the first key affects index usability.
Advanced: E-commerce database
Create suitable indexes for a Products table.
- Use ProductId as the clustered index key.
- Create a nonclustered index on SKU.
- Create a composite index on CategoryId and ProductName.
Challenge: Compare actual execution plans for indexed and non-indexed search queries.
7. Next Steps and Community
Previous article: Database Normalization – 1NF, 2NF, 3NF and BCNF
Next article: SQL Server Transactions – COMMIT, ROLLBACK and ACID Properties
For more details, read the official Microsoft documentation:
Try the library indexing examples and create your own indexes. Share your execution plans and discuss your indexing choices with other learners.