Welcome back. In the previous lesson, you learned normalization—how to design tables so data stays consistent and
does not drift into contradictions over time.
Now we’re moving into one of the most important topics for DBAs and performance: indexes.
This lesson will give you a correct mental model of indexing so you don’t fall into the two classic traps:
(1) thinking indexes are “free speed,” or (2) being afraid to use them.
Lesson description: You will learn what indexes are from a data-structure perspective, how they accelerate reads,
what they cost on writes, how SQL Server chooses between seeks and scans, what selectivity means,
what clustered vs nonclustered means at a conceptual level, what “covering” means, what fragmentation is,
and the most common indexing mistakes that create slow queries and bloated systems.
An index is a separate data structure that helps SQL Server find rows faster.
It is not magic, and it is not a setting that makes everything fast.
The easiest way to understand an index is the book analogy:
DBA rule: Indexes accelerate reads by reducing the amount of data SQL Server must touch.
But indexes cost storage and add work during writes.
Most database slowness is I/O-related: SQL Server has to read too many pages from memory or disk.
Indexes exist to reduce that workload.
When you run a query like “find customer by email,” SQL Server has two basic options:
Key idea: A well-designed index can turn a slow scan into a fast seek.
New DBAs often think “scan = bad, seek = good.” That is not always true.
SQL Server chooses a plan based on estimated cost.
DBA mindset: The goal is not “no scans.” The goal is efficient access patterns based on workload.
Selectivity describes how many rows match a filter.
Highly selective filters match very few rows (good for index seeks).
Low selectivity filters match many rows (indexes may not help much).
CustomerEmail = 'someone@example.com'Status = 'ACTIVE'DBA insight: Indexes are most valuable when they help SQL Server find a small subset of rows quickly.
Most SQL Server indexes are implemented using a B-tree structure.
You do not need to become a computer scientist, but you must understand the practical outcome:
That structure is what enables fast searching:
SQL Server navigates down the tree instead of reading every row.
A clustered index defines the physical order of the table’s data rows on disk (conceptually).
In SQL Server, the clustered index leaf level is the data.
Important rules:
DBA best practice (common): Many OLTP tables use an INT IDENTITY clustered key because inserts are sequential
(often fewer page splits and predictable performance).
A nonclustered index is a separate structure that stores:
(key columns) + a row locator so SQL Server can find the full row.
If the query needs columns not present in the index, SQL Server may perform a lookup to fetch the rest of the data.
(This can be efficient for small result sets, and expensive for large ones.)
Important rule: You can have multiple nonclustered indexes, but every index adds overhead to inserts/updates/deletes.
A query is covered by an index when SQL Server can satisfy the query using only the index,
without going back to the base table (or clustered index) to fetch additional columns.
SQL Server supports this using:
DBA mindset: Covering is powerful for critical queries, but do not “include everything.”
Over-wide indexes waste memory, increase storage, slow writes, and increase maintenance costs.
An index is useful only when SQL Server can use it efficiently.
Many slow queries happen because predicates are not SARGable
(Search ARGument Able), meaning SQL Server cannot perform an efficient seek.
LIKE '%text' (often forces scans).DBA takeaway: Indexing alone cannot fix bad query patterns. Indexing and query design must align.
Every time you insert, update, or delete rows, SQL Server must maintain:
DBA reality: Too many indexes can make writes slow, increase lock durations, and bloat the transaction log.
This is why “index everything” is not a strategy.
Indexes are stored in pages. When SQL Server needs to insert a row into the middle of an index page and that page is full,
SQL Server may perform a page split—creating a new page and moving rows.
Over time, page splits can create fragmentation (pages out of logical order).
Fragmentation can increase I/O for range scans and reduce cache efficiency.
An index is not just a structure—it also creates (or supports) statistics.
Statistics help SQL Server estimate how many rows will match a predicate.
Those estimates drive plan selection (seek vs scan, join type, memory grants).
DBA insight: Many performance problems happen because row estimates are wrong.
Wrong estimates often come from stale statistics or data distribution changes.
You will go deeper into statistics and cardinality estimation in later sections, but you must know now:
indexes and statistics are connected.
This wastes storage and slows writes without improving performance.
Always index for workload, not for theory.
Multiple indexes that start with the same key columns can be redundant.
DBAs aim for minimal, high-value indexes.
A poor clustered key can cause heavy page splits, fragmentation, and inefficient lookups.
The clustered key should be stable, narrow, and align with access patterns.
Not including necessary columns can cause expensive lookups.
Including too many columns creates bloated indexes.
Balance is the DBA skill.
If predicates are non-SARGable or conversions exist, indexes may be ignored.
Fixing query patterns can produce bigger wins than adding indexes.
When you decide whether to add or change an index, use this framework:
This is the same evidence-first workflow you learned in Section 1, applied to indexing.
In the next lesson, we will apply everything from Section 2 in a practical lab design exercise.
You will design a small, realistic schema, choose keys and constraints, model relationships,
and build the foundation for writing clean queries in Section 3.
Not a member yet? Register now
Are you a member? Login now