LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

SQL Server Indexes – Clustered, Nonclustered and Composite Indexes

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.

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.

  1. Searching can require reading many data pages.
  2. Large tables can increase the amount of reading.
  3. Repeated searches can consume additional database resources.
  4. 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 Librarian · needs one book
searches ISBN
Search Request ISBN = 9780131103627
checks the index tree
Nonclustered Index Finds the matching key
follows the row locator
Clustered Index Locates the complete book row
returns matching data
Book Found BookId 101 · Dennis Ritchie
Search input Index processing Retrieved record Current animation stage

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

Comparison of common SQL Server index types
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.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.