Module 01 · Questions 1–12

Indexes

The most-asked SQL Server topic by a distance. Know clustered vs non-clustered cold, and be ready for the follow-up: when does an index make things worse?

What is a database index?

In one line

A separate sorted structure — a B-tree — that lets SQL Server jump straight to the rows it needs instead of reading the whole table.

WITHOUT AN INDEX WITH AN INDEX SELECT * FROM Orders SELECT * FROM Orders WHERE CustomerId = 4821 WHERE CustomerId = 4821 read all 5,000,000 rows root page → intermediate → leaf = table scan = index seek, ~3 page reads

The book analogy works well out loud: without an index you read every page looking for the word; with one you look it up and turn to page 412. The cost is that the index has to be kept up to date on every write, and it occupies storage.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
    ON dbo.Orders (CustomerId)
    INCLUDE (OrderDate, Total);

30-second interview answer

An index is a separate B-tree structure that stores the key columns in sorted order with a pointer back to the row, so the engine can seek directly to the rows a query needs instead of scanning the table. It makes reads dramatically faster on selective filters, joins and sorts. The trade-off is that every insert, update and delete has to maintain it, and it takes storage — so I index for the queries that actually run, not every column.

Clustered vs non-clustered index

In one line

The clustered index IS the table, stored in the index's order. A non-clustered index is a separate copy of the key columns plus a pointer back to the row.

CLUSTERED (one per table — the data itself, sorted by the key) root → intermediate → LEAF = the actual data rows [1, Ajay, Jaipur, ...] [2, Ravi, Delhi, ...] NON-CLUSTERED (a separate structure pointing back) root → intermediate → LEAF = key + row locator ["Delhi", → row 2] ["Jaipur", → row 1] │ └─ key lookup into the clustered index

	Clustered	Non-clustered
How many	One per table	Up to 999
Leaf level holds	The data rows	Key + row locator
Determines physical order	✅ Yes	❌ No
Extra lookup needed?	Never	Yes, unless the index covers the query
Created by default on	The PRIMARY KEY	UNIQUE constraints
Best for	Range scans, the main access path	Specific lookups, additional filters

Choosing the clustered key

It should be narrow, unique, static and ever-increasing — which is why INT IDENTITY is the classic choice. Every non-clustered index stores the clustered key as its row locator, so a wide clustered key inflates every other index on the table. A random GUID is the usual mistake: it causes page splits and fragmentation on every insert.

30-second interview answer

A clustered index defines the physical order of the table — its leaf level is the data itself, so there's only one per table and it's created on the primary key by default. A non-clustered index is a separate structure holding the key columns and a pointer back to the row, so you can have many, and reading extra columns costs a key lookup unless the index covers the query. The clustered key should be narrow and increasing, because every non-clustered index carries a copy of it.

Q3How many clustered and non-clustered indexes can a table have?

One clustered — the rows can only be sorted one way physically — and up to 999 non-clustered, though in practice 5–10 is already a lot.

A table with no clustered index is a heap: rows sit in no particular order and non-clustered indexes point to a physical RID instead of a key. Heaps are fine for staging tables, rarely for anything else.

Composite index — and is it the same as a non-clustered index?

In one line

A composite index is simply one index over several columns. It's an independent concept from clustered/non-clustered.

Two independent axes: single column composite (multi-column) clustered PK (Id) PK (OrderId, LineNo) non-clustered IX (CustomerId) IX (CustomerId, OrderDate) "Composite" says HOW MANY COLUMNS. "Clustered/non-clustered" says WHERE THE DATA LIVES.

CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
    ON dbo.Orders (CustomerId, OrderDate);     -- composite, non-clustered

-- up to 32 key columns (16 before SQL Server 2016), max 900/1700 bytes of key

So the answer to "is composite the same as non-clustered?" is no — a composite index can be either. That's precisely the confusion the question is testing.

Why does column order matter in a composite index?

In one line

The index is sorted by the first column, then by the second within it — so it can only be used efficiently when your filter includes a leading prefix.

INDEX ON (City, Age) — think of a phone book sorted by City, then Age Delhi 22 Delhi 31 ← all Delhi rows are together, sorted by Age Delhi 45 Jaipur 19 Jaipur 40 WHERE City = 'Delhi' ✅ seek — one contiguous range WHERE City = 'Delhi' AND Age > 30 ✅ seek — range within the range WHERE Age > 30 ❌ scan — Age values are scattered everywhere

Query	Index (City, Age)	Index (Age, City)
`WHERE City = 'Delhi'`	✅ Seek	❌ Scan
`WHERE Age = 30`	❌ Scan	✅ Seek
`WHERE City = 'Delhi' AND Age = 30`	✅ Seek	✅ Seek

How to order the columns

Equality predicates first, then range predicates, then anything only used for sorting. Among equality columns, put the most selective first. And this is why two indexes on (A, B) and (A) are redundant — the first already serves queries on A alone.

Covering index and included columns

In one line

A covering index contains every column the query needs, so SQL Server never touches the table. INCLUDE adds those extra columns to the leaf level without making them part of the sort key.

SELECT OrderDate, Total
FROM   Orders
WHERE  CustomerId = 4821;

-- without INCLUDE: seek on the index, then a KEY LOOKUP per row to fetch OrderDate/Total
CREATE INDEX IX_Orders_Customer ON Orders (CustomerId);

-- covering: everything the query needs is in the index → no lookup at all
CREATE INDEX IX_Orders_Customer ON Orders (CustomerId) INCLUDE (OrderDate, Total);

	Key column	INCLUDE column
Sorted	✅ Yes	❌ No
Usable for seeking / filtering	✅	❌ (only returned)
Counts toward the 900/1700-byte key limit	✅	❌
Stored at	Every level of the B-tree	Leaf level only

The rule: columns you filter, join or sort on go in the key; columns you merely return go in INCLUDE. Key lookups are one of the most common causes of a query being far slower than it looks.

Index seek vs index scan

In one line

A seek navigates the tree straight to the matching rows. A scan reads the whole index. In an execution plan, a scan on a big table is your warning sign.

	Index Seek	Index Scan
Reads	Only matching rows	Every row in the index
Cost	O(log n)	O(n)
Wanted?	✅ Usually	Only for small tables or genuinely broad queries

Why a seek turns into a scan:

No index supports the predicate.
The predicate isn't SARGable — the column is wrapped in a function (Q8).
The filter isn't selective — returning 60% of the table, a scan really is cheaper.
Stale statistics make the optimiser think the filter is broad.
The leading column of a composite index isn't in the WHERE clause (Q5).

Nuance worth adding

A scan isn't automatically bad. On a 200-row lookup table it's the right choice, and the optimiser knows it. What matters is a scan on a large table where a selective filter should have produced a seek.

Q8What makes a predicate SARGable?

SARGable (Search ARGument able) means SQL Server can use an index seek for it. Wrapping the column in a function destroys that.

-- ❌ non-SARGable: the function hides the column's value from the index
WHERE YEAR(OrderDate) = 2026
WHERE UPPER(Name) = 'EVOLO'
WHERE Total * 1.18 > 1000
WHERE Name LIKE '%evolo%'

-- ✅ SARGable: the column stands alone
WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
WHERE Name = 'Evolo'                    -- with a case-insensitive collation
WHERE Total > 1000 / 1.18
WHERE Name LIKE 'evolo%'                -- leading wildcard is the killer, trailing is fine

Implicit conversions do the same damage — an NVARCHAR parameter compared to a VARCHAR column shows as CONVERT_IMPLICIT in the plan and forces a scan.

When can an index hurt performance?

Cost	What happens
Write amplification	Every INSERT/DELETE updates every index; an UPDATE updates each index containing the changed column
Storage & backups	Indexes can easily exceed the size of the data, inflating backups and restore time
Memory	Index pages compete for the buffer pool with actual data
Fragmentation	Random inserts cause page splits, which slow scans down
Worse plans	A tempting but unsuitable index can lead the optimiser astray
Duplicates	Overlapping indexes cost writes and give nothing back

-- unused and duplicate indexes: find them before adding more
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name,
       s.user_seeks, s.user_scans, s.user_lookups, s.user_updates
FROM   sys.indexes i
LEFT   JOIN sys.dm_db_index_usage_stats s
       ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE  i.type_desc = 'NONCLUSTERED'
ORDER  BY s.user_updates DESC;   -- lots of updates, no seeks = pure cost

30-second interview answer

Indexes speed reads and slow writes. Every insert or delete has to maintain every index, an update maintains the ones containing the changed columns, and they all consume storage, backup time and buffer-pool memory. A low-selectivity index is never used but still paid for, and overlapping indexes are pure cost. So on an OLTP table I keep a small number of targeted indexes and check sys.dm_db_index_usage_stats for ones with many updates and no seeks.

Q10How do indexes affect INSERT, UPDATE and DELETE?

Table with 6 non-clustered indexes: INSERT → 1 write to the clustered index + 6 index writes = 7 operations DELETE → same UPDATE → only the indexes containing the changed column(s)

So an UPDATE Orders SET Status = ... is cheap if no index contains Status, and expensive if three do. On a high-write OLTP table, every extra index is a real transaction-throughput cost; on a reporting table read far more than written, extra indexes are usually worth it.

Q11What is a filtered index?

A non-clustered index with a WHERE clause, so it only indexes the rows you actually query — smaller, faster to scan and cheaper to maintain.

CREATE NONCLUSTERED INDEX IX_Orders_Pending
    ON Orders (CreatedAt)
    WHERE Status = 'Pending';     -- 2% of a 50-million-row table

Ideal for soft-delete flags (WHERE IsDeleted = 0), status columns and sparse columns. The catch: the query's predicate must match the filter for the index to be used, and parameterised queries sometimes won't match it unless the constant is literal.

Q12What is index fragmentation, and what do you do about it?

Over time, inserts and updates cause page splits, so the logical order of pages drifts from their physical order and scans have to read more pages.

SELECT OBJECT_NAME(object_id), index_id, avg_fragmentation_in_percent, page_count
FROM   sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED')
WHERE  page_count > 1000;

ALTER INDEX IX_x ON dbo.t REORGANIZE;                    -- ~5–30%: online, light
ALTER INDEX IX_x ON dbo.t REBUILD WITH (ONLINE = ON);    -- >30%: heavier, resets stats

Two practical notes: ignore fragmentation on small indexes (under ~1,000 pages — it's noise), and a FILLFACTOR below 100 leaves room on each page to reduce future splits at the cost of more space.