Skip to main content

Indexes: Clustered, Nonclustered and Covering – SQL Server | Lesson 26

Published / updated

Introduction​

The SMS now holds 600,000 attendance rows and 120,000 marks. The principal's dashboard asks "how many students were present today?" many times a day. Without help, SQL Server reads the whole attendance table to answer it.

An index is a sorted structure that lets SQL Server jump straight to the rows it needs. In this lesson, you measure three everyday queries, add indexes, and measure again.

By the end of this lesson, you will be able to:

  • Explain clustered and nonclustered indexes.
  • Tell an index seek from a scan, and explain a key lookup.
  • Build covering indexes with INCLUDE.
  • Choose the order of columns in a multi-column index.
  • Measure work with SET STATISTICS IO ON, and weigh the cost of indexes on writes.

Why this matters​

Slow queries are the most common complaint about database applications, and missing indexes are the most common cause. Every extra page read is memory, disk, CPU, and locks. With forty teachers saving attendance while the dashboard reads it, those costs add up.

Indexes are also easy to overdo. Each one slows every insert and update a little, and takes space. The skill is adding the few indexes that matter, and proving that they help.

The concept​

Example: what an index is​

Think of the index at the back of a textbook. To find "photosynthesis", you do not read every page; you look it up in the alphabetical index and jump to page 214. A database index is the same idea: a sorted list of values, each pointing to where the rows are.

SQL Server stores data in pages of 8 KB. A query's cost is mostly how many pages it reads. SET STATISTICS IO ON shows that number as logical reads.

Example: clustered and nonclustered​

KindWhat it isHow many per tableSMS example
Clustered indexThe table itself, stored in key orderOnePK_Students on Id
Nonclustered indexA separate sorted copy of some columns, plus a pointer to the rowManyIX_Students_LastName_FirstName

Every primary key in the SMS is clustered by default. Every UNIQUE constraint also created a nonclustered index. So the database already had indexes on Id, AdmissionNumber, and (EnrollmentId, AttendanceDate) before this lesson.

Example: seek, scan, and lookup​

OperationMeaningCost
Index seekJump to the matching values in a sorted indexFew pages
Index scanRead the whole index from start to endMany pages
Key lookupFor each row found in a nonclustered index, fetch missing columns from the tableOne extra read per row

A seek needs an index whose first column matches the filter. An index on (EnrollmentId, AttendanceDate) helps "attendance of one student", but not "attendance of one date for everyone", because the date is the second column. A phone book sorted by last name, then first name, finds every "Sharma" fast, but not everyone named "Aarav".

Example: covering indexes​

INCLUDE adds columns to the bottom level of a nonclustered index without sorting by them. If an index holds every column a query needs, it covers the query, and no key lookups are needed:

Example
CREATE INDEX IX_AttendanceRecords_AttendanceDate
    ON dbo.AttendanceRecords (AttendanceDate)
    INCLUDE (Status);

Sorted by date for the seek, with Status included for the count.

Apply it to the SMS​

Step 1: measure before​

Run this demo script and look at the Messages tab. It counts one day's attendance, searches a student by name, and lists one enrollment's marks:

queries/026-index-demo.sql
-- Lesson 26: measure three common queries.
-- Run once before database/026-indexes.sql and once after, and compare the logical reads
-- shown in the Messages tab (pages of 8 KB that SQL Server had to read).
USE SchoolDb;
GO

SET STATISTICS IO ON;

-- 1. Whole-school attendance for one day.
SELECT Status, COUNT(*) AS Students
FROM dbo.AttendanceRecords
WHERE AttendanceDate = '2026-07-01'
GROUP BY Status;

-- 2. Search students by last name.
SELECT Id, FirstName, LastName
FROM dbo.Students
WHERE LastName = N'Sharma'
  AND FirstName = N'Aarav';

-- 3. All marks of one enrollment (Aarav this year): a report card.
SELECT ExamSubjectId, MarksObtained, IsAbsent
FROM dbo.Marks
WHERE EnrollmentId = 13;

SET STATISTICS IO OFF;
GO

Step 2: add the indexes​

Save this as database\026-indexes.sql and run it:

Show database/026-indexes.sql
database/026-indexes.sql
-- Lesson 26: indexes for the queries the SMS runs most.
-- Safe to run more than once: each index is created only if it does not exist yet.
USE SchoolDb;
GO

-- Attendance by date for the whole school (dashboard, daily reports).
-- INCLUDE stores Status in the index too, so the table itself is not touched.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_AttendanceRecords_AttendanceDate')
    CREATE INDEX IX_AttendanceRecords_AttendanceDate
        ON dbo.AttendanceRecords (AttendanceDate)
        INCLUDE (Status);

-- Student search by name.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Students_LastName_FirstName')
    CREATE INDEX IX_Students_LastName_FirstName
        ON dbo.Students (LastName, FirstName);

-- All marks of one enrollment (report cards). The existing unique index starts with
-- ExamSubjectId, so it cannot help a search by EnrollmentId alone.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_Marks_EnrollmentId')
    CREATE INDEX IX_Marks_EnrollmentId
        ON dbo.Marks (EnrollmentId)
        INCLUDE (ExamSubjectId, MarksObtained, IsAbsent);

-- Foreign keys used in joins: payments by invoice, invoices by enrollment.
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeePayments_FeeInvoiceId')
    CREATE INDEX IX_FeePayments_FeeInvoiceId
        ON dbo.FeePayments (FeeInvoiceId);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_FeeInvoices_EnrollmentId')
    CREATE INDEX IX_FeeInvoices_EnrollmentId
        ON dbo.FeeInvoices (EnrollmentId);
GO

-- Check: every index in the database that is not a primary key.
SELECT
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_unique AS IsUnique
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON t.object_id = i.object_id
WHERE i.is_primary_key = 0
  AND i.name IS NOT NULL
  AND OBJECT_NAME(i.object_id) IN ('AttendanceRecords', 'Students', 'Marks', 'FeePayments', 'FeeInvoices')
ORDER BY TableName, IndexName;
GO

The last query lists the indexes on these tables: five new IX_ indexes next to the UQ_ indexes that the unique constraints created in Module 3.

Example: before and after​

Run the demo script again. The logical reads on a laptop:

QueryBeforeAfterWhy
Attendance for 1 July (whole school)3,35138Seek on the date, Status included
Student named Aarav Sharma1262Seek on (LastName, FirstName)
Marks of one enrollment3363Seek on EnrollmentId

The answers are identical; only the work changed. Indexes never change what a query returns, only how SQL Server finds it.

The attendance count dropped from 3,351 pages, about 26 MB read, to 38 pages. Your numbers can differ slightly, but the size of the improvement will be the same.

Example: why the marks query needed its own index​

Marks already had UQ_Marks_ExamSubjectId_EnrollmentId. It did not help "marks of enrollment 13", because EnrollmentId is its second column. The new index puts EnrollmentId first and includes the three columns the report card reads.

Choose column order by how the column is searched: columns compared with = first, the most-used filter first, and range columns such as dates last.

Example: the filtered indexes you already have​

You built three special indexes in Module 3 without measuring speed at all: UQ_AcademicYears_IsCurrent, UQ_Users_TeacherId, and UQ_Users_GuardianId. They are filtered indexes: they include only rows that match a WHERE condition, such as IsCurrent = 1.

Filtered indexes are small, because they skip most rows, and they can enforce rules like "only one current year". They also speed up queries that use the same condition, such as WHERE IsCurrent = 1, which almost every SMS query does.

Example: the cost of indexes​

Every index is a copy that SQL Server must update on every insert, update, and delete of its columns. Saving one attendance row now updates the table, the unique index, and the date index: three writes instead of two.

Good candidatesPoor candidates
Foreign key columns used in joinsColumns nobody filters or joins on
Columns in frequent WHERE filtersTiny tables that fit in a page or two
Columns in ORDER BY of frequent queriesColumns with very few values on their own, such as Gender
Queries that run many times a dayTables with heavy writes and rare reads

Example: index suggestions from SQL Server​

While queries run, SQL Server notes indexes it wished it had. You can read those notes:

Example
SELECT TOP (5)
    d.statement AS TableName,
    d.equality_columns AS EqualityColumns,
    d.inequality_columns AS RangeColumns,
    d.included_columns AS IncludeColumns
FROM sys.dm_db_missing_index_details AS d
WHERE d.database_id = DB_ID();

The list fills up as the server works and is cleared when SQL Server restarts, so on a fresh laptop database it may be empty. Treat each row as a hint, not an order: suggestions often overlap, and following all of them creates the "23 indexes" problem below. SSMS shows the same hint in green text above an execution plan (Lesson 27).

Example: which indexes are really used​

sys.dm_db_index_usage_stats counts, per index, how many seeks, scans, and updates it has had since the last restart:

Example
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks AS Seeks,
    s.user_scans AS Scans,
    s.user_updates AS Updates
FROM sys.dm_db_index_usage_stats AS s
INNER 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();

An index with many updates and no seeks or scans, after weeks of normal use, costs every write and helps no read. That is a candidate to drop, after checking with the team.

Example: keeping indexes healthy​

As rows are inserted and deleted, index pages can end up half empty or out of order. This is fragmentation. Check it with:

Example
SELECT
    i.name AS IndexName,
    CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5, 1)) AS FragmentationPercent,
    ps.page_count AS Pages
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.AttendanceRecords'), NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id;
IndexNameFragmentationPercentPages
PK_AttendanceRecords0.03334
UQ_AttendanceRecords_EnrollmentId_AttendanceDate0.11559
IX_AttendanceRecords_AttendanceDate0.02062

Freshly built indexes are almost unfragmented. On a busy system, a weekly job runs ALTER INDEX ... REORGANIZE or REBUILD on badly fragmented large indexes, and keeps the statistics up to date: the summaries SQL Server uses to estimate row counts.

Common mistakes​

  1. An index for every column. Writes slow down and storage grows. Fix: index for real, frequent queries, and measure.
  2. Wrong column order. An index on (A, B) does not help a filter on B alone. Fix: put the filtered column first.
  3. Functions on indexed columns. YEAR(AttendanceDate) = 2026 cannot seek. Fix: compare the bare column with a range (Lesson 27).
  4. Forgetting foreign keys. Joins and deletes scan the child table. Fix: index foreign key columns that are used in joins.
  5. Guessing instead of measuring. Fix: compare logical reads before and after.

In real projects​

From 16 years of .NET projects

Most "the database is slow" tickets I have closed were fixed by one or two indexes, chosen after looking at the actual query. The fastest wins are almost always foreign keys that were never indexed and a date column that every report filters on.

I have also deleted many indexes: a table with 23 of them, most added by different developers "just in case". Inserts took seconds. SQL Server records how often each index is used, in sys.dm_db_index_usage_stats; indexes that are never read are pure cost.

Best practices​

  • Measure with SET STATISTICS IO ON before and after every index.
  • Index foreign keys used in joins, and columns in frequent filters.
  • Put equality columns first and range columns last in multi-column indexes.
  • Use INCLUDE to cover frequent queries and remove key lookups.
  • Review unused indexes from time to time, and drop the ones that only cost.
  • Treat missing-index suggestions as hints, and combine overlapping ones.

Recap​

  • An index is a sorted copy of some columns that lets SQL Server seek instead of scan.
  • The clustered index is the table itself; nonclustered indexes are extra copies.
  • A seek uses the index's first column; a key lookup fetches missing columns.
  • INCLUDE makes covering indexes.
  • Three indexes cut reads from 3,351, 126, and 336 pages to 38, 2, and 3.
  • Each index makes writes a little slower; add only the ones that earn their place.
  • Usage statistics show which indexes are used; fragmentation checks show which need maintenance.
  • Filtered indexes cover only matching rows, so they stay small.

Homework​

Library module task: The librarian often asks "which books does this student have?". Create an index on BookIssues that supports a search by StudentId and covers the copy, the dates, and the return date.

A correct result looks like: IX_BookIssues_StudentId appears in sys.indexes, and the script runs twice without errors.

Show the solution
library-assignment/026-library-index.sql
-- Library homework, Lesson 26: "which books does this student have?" is asked often.
USE SchoolDb;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_BookIssues_StudentId')
    CREATE INDEX IX_BookIssues_StudentId
        ON dbo.BookIssues (StudentId)
        INCLUDE (BookCopyId, IssuedOn, DueOn, ReturnedOn);
GO

Quiz​

  1. How many clustered indexes can a table have?

    1. None
    2. One
    3. Two
    4. As many as needed
    Show answer

    Answer: B. One The clustered index is the table's own order, so there is only one.

  2. An index on (EnrollmentId, AttendanceDate) exists. Which filter can it seek on?

    1. AttendanceDate = '2026-07-01' only
    2. EnrollmentId = 13
    3. Status = 'Absent'
    4. None of them
    Show answer

    Answer: B. EnrollmentId = 13 A seek needs the index's first column in the filter.

  3. What does INCLUDE (Status) add to an index?

    1. Sorting by Status
    2. A copy of Status stored in the index, so lookups are not needed
    3. A unique rule on Status
    4. A foreign key
    Show answer

    Answer: B. A copy of Status stored in the index, so lookups are not needed Included columns are stored, not sorted, making the index covering.

  4. What does SET STATISTICS IO ON show?

    1. The execution plan
    2. How many pages each table read
    3. How long the query ran
    4. Which locks were taken
    Show answer

    Answer: B. How many pages each table read Logical reads count 8 KB pages, a good measure of a query's work.

  5. What is the cost of adding an index?

    1. Reads become slower
    2. Every insert, update, and delete on its columns must update it too
    3. The table is locked forever
    4. There is no cost
    Show answer

    Answer: B. Every insert, update, and delete on its columns must update it too Indexes speed up reads and slow down writes a little, and take space.

Interview questions​

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)​

F What is an index? concept

A sorted structure that lets SQL Server find rows without reading the whole table, like the index at the back of a book. In the SMS, an index on AttendanceDate cut one day's attendance query from 3,351 page reads to 38.

See the example · Permalink

F What is the difference between a clustered and a nonclustered index? concept

The clustered index is the table itself, stored in key order; there is only one. A nonclustered index is a separate sorted copy of some columns with a pointer to the row; a table can have many. Primary keys are clustered by default.

See the example · Permalink

F What is the difference between an index seek and an index scan? concept

A seek jumps straight to the matching values in a sorted index and reads few pages. A scan reads the whole index or table. Scans are fine for small tables or when most rows are needed, but a scan on a big table for a few rows usually means a missing or unusable index.

See the example · Permalink

Mid (3-5 yrs)​

M What is a covering index, and what does INCLUDE do? concept

A covering index contains every column a query needs, so no key lookups to the table are required. INCLUDE adds columns to the index's leaf level without sorting by them, such as INCLUDE (Status) on the attendance date index.

See the example · Permalink

M Why does the order of columns in a multi-column index matter? concept

SQL Server can seek only on the leading column or columns. An index on (ExamSubjectId, EnrollmentId) helps a search by paper, but not a search by enrollment alone, which is why the SMS needed a separate index starting with EnrollmentId.

See the example · Permalink

M What is the cost of adding indexes? concept

Every insert, update, and delete on the indexed columns must also update each index, so writes get slower, and each index takes storage and memory. Add indexes for frequent, measured queries, and drop indexes that are never used.

See the example · Permalink

Senior (6-9 yrs)​

S The daily attendance report is slow. Walk through how you would decide which index to add. scenario

Measure the query with SET STATISTICS IO and the actual plan, and find the scan on the big table. Index the filtered column first, here AttendanceDate, and include the columns the query reads, here Status. Measure again: in the SMS, reads dropped from 3,351 to 38.

How to answer: Follow measure, analyze, change, measure. Explain the column choice (filter first, include the rest) and confirm the plan changed to a seek. Mention checking the write cost on a busy table.

See the example · Permalink

S A table has 23 indexes and inserts are slow. What do you do? scenario

Check sys.dm_db_index_usage_stats over a representative period to find indexes with many updates and no reads, and look for indexes that duplicate or overlap others. Drop or merge them after review, keeping the ones that serve important queries.

How to answer: Show a data-driven clean-up: usage statistics, overlap analysis, and agreement with the team. Mention that usage stats reset on restart, so measure over a long enough period, and keep indexes that support constraints.

See the example · Permalink

Architect / Lead (10+ yrs)​

A How do you plan an indexing strategy for a new system? design

Start with primary keys, unique constraints, and indexes on foreign keys used in joins. Then add indexes for the most frequent and most important queries, tested with realistic data volumes. Review usage and missing-index data after launch, and maintain indexes and statistics on a schedule.

How to answer: Describe the lifecycle: design-time basics, query-driven additions, load testing, and production review. Balance read speed against write cost per table, such as the busy attendance table versus rarely written masters.

See the example · Permalink

Resources​

  • Code for this lesson: git tag lesson-26-indexes (code repository coming soon)
  • Files: queries/026-index-demo.sql, database/026-indexes.sql, library-assignment/026-library-index.sql
  • Video: coming soon

What's next​

Indexes only help if queries can use them. Next, you read execution plans and fix queries that hide their columns from indexes. Lesson 27: Execution plans and tuning

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.