Nonclustered Index in SQL
Introduction
A nonclustered index is a separate database structure that organizes selected key values and stores enough information to locate corresponding table rows. In Microsoft SQL Server, it normally uses a balanced B+ tree. Its leaf level contains index keys, row locators, and any explicitly included columns rather than every column from the base table.
This separation lets one table support several useful access paths. Employee rows might be clustered by employee ID while nonclustered indexes support searches by email, department and salary, or city. The optimizer can navigate whichever structure offers the lowest estimated cost for a specific query.
The official SQL Server spelling is nonclustered, although tutorials frequently write non-clustered. This page uses SQL Server syntax and terminology while explaining how related secondary indexes differ in MySQL, PostgreSQL, Oracle, and SQLite.
What Is a Nonclustered Index?
A nonclustered index is an access path stored separately from the base table. Its keys are maintained in sorted logical order, allowing the database to navigate to an exact value, a key range, or an ordered sequence without scanning every table row. Each matching leaf entry either contains all data the query needs or provides a locator for fetching the remaining columns.
The index does not determine the table's primary storage organization. A table may have many nonclustered indexes because each structure maintains its own key order. That flexibility is valuable, but every additional structure consumes storage and must be maintained during writes.
Why Nonclustered Indexes Are Needed
A single clustered key cannot serve every filter, join, and ordering requirement. An employee table clustered by employee_id may also need fast lookups by normalized email, department, city, hire date, or manager. Nonclustered indexes create secondary routes to the same rows.
They are especially effective for selective predicates that return a small fraction of a large table. They can also enforce alternate-key uniqueness, cover frequently executed queries, avoid sorts, and reduce the amount of data scanned for reporting. The optimizer still decides whether using an index is cheaper than scanning.
A Library Analogy
Imagine books arranged on shelves by catalog number. The physical shelf order resembles the table's primary organization. Readers also search by author, title, and subject, so the library maintains separate catalogs. Each catalog entry provides a book location rather than containing the full book.
A database nonclustered index plays that catalog role. It is faster to search a compact ordered structure, but the final row may still require a second access. Adding every possible catalog would make updates and storage impractical, so useful indexes must reflect real demand.
Sample Database
CREATE TABLE dbo.employees (
employee_id INT NOT NULL,
employee_name VARCHAR(100) NOT NULL,
department_id INT NOT NULL,
salary DECIMAL(12, 2) NOT NULL,
city VARCHAR(80) NOT NULL,
email VARCHAR(255) NOT NULL,
hired_at DATE NOT NULL,
CONSTRAINT PK_employees
PRIMARY KEY CLUSTERED (employee_id)
);
The clustered primary key organizes base rows by employee ID. Additional indexes can support other access patterns without changing that organization.
How a Nonclustered Index Works
SQL Server begins at the B+ tree root, follows key ranges through any intermediate levels, and reaches matching leaf entries. A small index may have only a root and leaf level, while a large one can require several intermediate levels. Balanced depth keeps navigation predictable as values grow.
At the leaf, SQL Server returns stored values or follows row locators to the base table. On a clustered table the locator is the clustered key. On a heap it is a physical row identifier. That distinction determines whether an execution plan shows a Key Lookup or a RID Lookup.
Creating a Nonclustered Index
CREATE NONCLUSTERED INDEX IX_employees_city
ON dbo.employees (city);
The key entries are ordered by city and identify matching employees through their clustered keys. A query filtering a selective city can seek that value. If the table is tiny or most employees live in the requested city, SQL Server may correctly choose a clustered scan instead.
Single-Column Index
CREATE INDEX IX_employees_salary
ON dbo.employees (salary);
In SQL Server, CREATE INDEX without the clustered keyword creates a nonclustered index by default. A salary index can support equality or range predicates, but returning every employee column may require base-row lookups. The index's usefulness depends on selectivity, output width, and frequency.
Composite Nonclustered Index
CREATE INDEX IX_employees_department_salary
ON dbo.employees (department_id, salary);
This index is ordered first by department and then by salary within each department. It can efficiently support equality on department followed by a salary range. It can also support a department-only filter, but a salary-only search generally cannot navigate the leading department groups as directly.
Column Order Matters
Composite order should reflect actual predicates and ordering. Equality columns commonly precede range columns because a range usually ends direct navigation for keys that follow. Sorting requirements, selectivity, grouping, and multiple query patterns can change that decision.
The slogan "most selective column first" is incomplete. If every important query specifies department_id and then filters salary, department belongs first even when salary has more distinct values. Validate the complete workload with plans.
Unique Nonclustered Index
CREATE UNIQUE NONCLUSTERED INDEX UX_employees_email
ON dbo.employees (email);
A unique index enforces an alternate business key while providing fast lookup. This is appropriate only when the data rule is truly unique under the configured collation and null semantics. Email normalization and case rules must be settled before relying on this constraint.
Declaring genuine uniqueness can also help the optimizer estimate that a lookup returns at most one row.
Nonclustered Index on a Clustered Table
When the base table has a clustered index, nonclustered leaf entries use the clustered key as their row locator. SQL Server carries those clustered-key columns within the nonclustered structure even when they are not explicitly listed in the index definition.
A narrow clustered key therefore reduces every nonclustered index. A wide composite clustered key is repeated throughout secondary structures, reducing page density and increasing memory, storage, logging, and I/O costs.
Nonclustered Index on a Heap
A heap has no clustered index. Its nonclustered entries use a row identifier, commonly called a RID, derived from file, page, and slot placement. SQL Server can follow that locator to the heap row.
Variable-length updates can create forwarded records when a heap row no longer fits on its original page. A RID may then lead to a forwarding location before reaching the moved row, adding work. Heaps can still be appropriate for staging and specific load patterns, but they require intentional design.
Key Lookup
SELECT employee_name, salary, city, email
FROM dbo.employees
WHERE city = 'Chicago';
The city index contains the city key and clustered employee ID, but it does not explicitly store name, salary, or email. SQL Server may seek IX_employees_city and perform a Key Lookup into the clustered index for each match. This nested-loops pattern can be excellent for a few rows and expensive for thousands.
The lookup is not automatically a defect. Compare its execution count, rows, reads, and elapsed time with wider-index and scan alternatives.
RID Lookup
If the same index exists on a heap, the plan may show a RID Lookup instead. Each nonclustered entry points to a heap row location rather than a clustered key. The operation retrieves columns absent from the nonclustered index.
Key Lookup and RID Lookup solve the same broad need but target different base structures. Converting a heap to a clustered table can rebuild nonclustered indexes because their locators must change.
Covering Index
An index covers a query when it contains every column needed for predicates, joins, ordering, and output. SQL Server can answer from the index without a separate base-row lookup, often reducing reads significantly for frequent selective queries.
CREATE INDEX IX_employees_city_cover
ON dbo.employees (city)
INCLUDE (employee_name, salary, email);
Coverage is query-specific. Adding every table column creates a large duplicate structure that harms writes and cache density. Cover only proven high-value queries.
Key Columns vs Included Columns
Key columns determine B+ tree order and support direct navigation. Included columns are stored only at the leaf level and provide output coverage without participating in key order. Included columns can use some data types that are unsuitable as keys and do not consume the key-column count in the same way.
They still occupy space and must be updated when values change. A column needed for filtering or ordering usually belongs in the key; a column needed only for output is a candidate for INCLUDE.
Filtered Nonclustered Index
CREATE INDEX IX_employees_active_department
ON dbo.employees (department_id)
INCLUDE (employee_name, email)
WHERE employment_status = 'ACTIVE';
A filtered index stores only rows satisfying a stable predicate. It can be smaller, more selective, and cheaper to maintain than a full-table index for active accounts, unprocessed jobs, open orders, or non-null values. The query predicate must be compatible with the filter for the optimizer to use it.
Parameterization, data types, and null logic can affect matching. PostgreSQL and SQLite use the term partial index for a related concept.
Indexes and the WHERE Clause
Equality and bounded-range predicates often align well with B+ tree keys. The greatest benefit usually appears when a predicate returns a small or otherwise useful subset. If most rows qualify, scanning a compact index or the base table may be cheaper than repeated lookups.
Keep predicates searchable. Functions on the indexed column, implicit conversions, mismatched collations, or arithmetic transformations can prevent direct seeks unless an appropriate computed-column or expression strategy exists.
Sargable Predicates
-- Less searchable with a normal hired_at index
WHERE YEAR(hired_at) = 2026
-- Exposes a direct date range
WHERE hired_at >= '2026-01-01'
AND hired_at < '2027-01-01'
The second form allows the optimizer to seek the lower boundary and scan until the upper boundary. It also states the time interval precisely. Computed and expression indexes may support transformed predicates, but their syntax and matching rules vary by database.
Indexes and JOIN
Foreign-key and relationship columns are common nonclustered-index candidates. A selective parent set can use nested loops to seek matching child rows. Ordered inputs may support merge joins, while large unsorted sets may favor hash joins.
Foreign-key constraints do not universally create child-column indexes automatically. Check existing composite indexes before adding one, because an index beginning with the foreign key may already serve the join.
Indexes and ORDER BY
CREATE INDEX IX_employees_department_salary_desc
ON dbo.employees (department_id, salary DESC)
INCLUDE (employee_name);
For one department, this index can return employees in descending salary order without an explicit Sort operator. SQL Server can often scan an index forward or backward, but mixed directions, null placement, joins, and other operators affect compatibility.
The query still needs ORDER BY to guarantee result order. Index order is an optimization opportunity, not an application contract.
Indexes and GROUP BY
An index ordered by grouping columns can support stream aggregation and may avoid a separate sort. A narrow index can also reduce pages read compared with the wide base table. Hash aggregation may still be cheaper depending on cardinality and memory.
A normal index does not precompute totals. Repeated expensive summaries may require indexed views, materialized views, summary tables, or analytical storage, with corresponding freshness and write costs.
Indexes and Range Queries
Nonclustered B+ trees are effective for date, price, amount, and sequence ranges. The engine can seek the first qualifying leaf entry and continue through adjacent entries until the boundary ends. A narrow index can make this scan much cheaper than reading complete rows.
If many output columns are required for a large range, lookup cost can dominate and the optimizer may prefer a base-table scan. Coverage, row width, and range size must be evaluated together.
Indexes and LIKE
-- Can use a compatible name index as a prefix range
WHERE employee_name LIKE 'Ali%'
-- Usually cannot seek a normal B+ tree from the left
WHERE employee_name LIKE '%lice%'
Collation and data type affect comparison behavior. Contains-style language searches often require SQL Server full-text search or a dedicated search platform rather than a conventional nonclustered index.
Indexes and NULL Values
SQL Server indexes can contain null key values, and predicates such as IS NULL can use a suitable index. A filtered index on WHERE processed_at IS NULL can be effective for a small pending queue, provided the query exposes a compatible predicate.
Uniqueness and null semantics differ among databases. Do not transfer SQL Server assumptions directly to PostgreSQL, MySQL, Oracle, or SQLite.
Index Seek vs Index Scan
A Nonclustered Index Seek navigates to a key or range, while a Nonclustered Index Scan reads a broad portion or all of the index leaf level. A scan of a narrow covering index can be an excellent plan because it reads less data than the clustered table.
Operator labels alone do not determine quality. Review rows read, rows returned, logical reads, lookup count, predicates, CPU, elapsed time, and concurrency.
Residual Predicates
A plan may seek using part of an index key and evaluate another condition after reading candidate entries. The latter is a residual predicate. For example, an index on (department_id, salary) can seek department and salary, while a city filter may be evaluated after those rows are found.
Adding city to the key is not automatically correct. Its position, selectivity, coverage value, and write cost must justify changing the index.
Cardinality and Selectivity
Selectivity describes how narrowly a predicate identifies rows. Unique email is highly selective; a status with two values usually is not. Low-cardinality columns can still be useful in composite or filtered indexes when paired with a selective range or when one value represents a small minority.
Data skew matters. A plan suitable for a rare status may be poor for the dominant status. Test representative parameters and review statistics rather than relying only on distinct-value counts.
INSERT Cost
Every insert must add an entry to each affected nonclustered index. SQL Server navigates the tree, writes leaf data, generates log records, and may split a full page. More indexes therefore reduce write throughput and increase log, memory, storage, backup, and replica work.
Bulk ingestion should be tested with the complete production index set. A design that performs well on an empty development table may behave very differently under concurrency.
UPDATE Cost
Updating a nonindexed column may still affect indexes that include it. Updating an index key can require deleting the old entry and inserting a new entry at another logical location. If a clustered key changes, its locator can also affect all nonclustered structures.
Index mutable columns only when read benefit justifies write amplification. Observe log bytes, locking, page splits, and transaction duration for realistic updates.
DELETE Cost
Deleting a row removes its entries from every index. Large retention purges can produce substantial logs, locks, ghost cleanup, and replica traffic. Wide covering indexes and numerous overlapping indexes multiply this work.
Batching, partition maintenance, and archival strategies may help, but those options must preserve referential integrity and recovery requirements.
Storage Overhead
Storage includes keys, locators, included columns, internal pages, page free space, and allocation structures. Compression may reduce size in supported editions and configurations but consumes CPU and must be tested. A small definition can grow very large on a high-row-count table.
Capacity planning should include the index itself, transaction logs, backups, replicas, build workspace, and maintenance headroom. Index value must exceed this lifecycle cost.
Checking Existing Indexes
SELECT
i.name,
i.type_desc,
i.is_unique,
i.has_filter,
i.filter_definition
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('dbo.employees')
AND i.index_id > 0
ORDER BY i.index_id;
Metadata confirms definitions but not business value. Join index columns and included columns through catalog views when comparing complete structures, and retain constraint ownership information before considering removal.
Checking Index Usage
SELECT
i.name,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
s.last_user_seek
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
ON s.database_id = DB_ID()
AND s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE i.object_id = OBJECT_ID('dbo.employees');
Usage counters reset after restart, failover, detach, and other events. One observation window can miss month-end or emergency workloads. Combine history, query plans, size, write rate, and owner knowledge before retiring an index.
Reading Execution Plans
Use the actual execution plan to see seeks, scans, lookup operators, seek predicates, residual predicates, estimates, actual rows, ordered properties, and warnings. A missing-index suggestion is a local estimate for one query, not a complete design recommendation. It may overlap existing structures or ignore write cost.
Plan regressions can arise from statistics, parameter sensitivity, data growth, memory pressure, or competing indexes. An index name appearing in a plan does not by itself prove the plan is efficient.
Measuring Performance
SET STATISTICS IO, TIME ON;
SELECT employee_id, employee_name, salary
FROM dbo.employees
WHERE department_id = 10
AND salary >= 70000;
Capture a baseline before creation and repeat with production-like rows, distributions, parameters, cache conditions, and concurrency. Compare latency percentiles, CPU, logical and physical reads, rows, waits, memory grants, lookup count, and write throughput.
A faster SELECT is not enough if the index materially harms dominant writes or maintenance windows.
Fragmentation and Page Density
Random insertions, deletions, and updates can produce logical fragmentation or low page density. Their impact depends on index size, storage, cache, scan patterns, and workload. A tiny index with a high fragmentation percentage rarely deserves urgent maintenance.
Use sys.dm_db_index_physical_stats with an appropriate scope, then interpret page count and density alongside observed performance.
Reorganizing an Index
ALTER INDEX IX_employees_department_salary
ON dbo.employees REORGANIZE;
Reorganization incrementally compacts and reorders leaf pages. It usually has lower resource peaks and operates online, but it does not rebuild the complete structure or perform the same statistics update as a rebuild. Use it only when measured conditions justify the operation.
Rebuilding an Index
ALTER INDEX IX_employees_department_salary
ON dbo.employees REBUILD
WITH (FILLFACTOR = 95);
Rebuild creates a fresh index, can change fill factor, and refreshes its statistics. It may require significant CPU, I/O, log, temporary space, and blocking. Online and resumable options depend on SQL Server version, edition, index type, and data types.
A lower fill factor reserves space but increases reads and storage. Choose it from page-split evidence, not as a universal setting.
Dropping a Nonclustered Index
DROP INDEX IX_employees_city
ON dbo.employees;
Before removal, confirm the index does not enforce a unique constraint and does not support infrequent critical workloads. Compare full definitions, including key order, filters, uniqueness, included columns, and ordering. Script recreation and monitor plans and latency after deployment.
Overlapping and Redundant Indexes
Indexes on (department_id) and (department_id, salary) overlap, but neither is automatically redundant. The narrower index may be much smaller, while the wider one may support a range or ordering. Included columns, filters, uniqueness, and workload frequency can change the conclusion.
Consolidation should reduce total cost without forcing one giant index to serve unrelated queries poorly.
SQL Server vs Other Databases
| Database | Related term | Locator or behavior |
|---|---|---|
| SQL Server | Nonclustered index | Clustered key or heap RID locator |
| MySQL InnoDB | Secondary index | Primary-key columns locate clustered rows |
| PostgreSQL | Secondary table index | Heap tuple identifiers with MVCC visibility rules |
| Oracle | B-tree index | ROWID commonly locates heap-organized rows |
| SQLite | Index | References rowid or primary-key organization |
The broad purpose is similar, but syntax, visibility, index-only access, null handling, filters, included data, and maintenance differ. Use the documentation for the deployed engine.
Employee Search Use Case
An HR application may retrieve active employees by department and salary while displaying name and email. A filtered composite index with output columns can support this path efficiently. Name contains-search should not be forced into the same structure when full-text search is more appropriate.
E-Commerce Orders Use Case
Customer order history commonly filters by customer ID and order date, then displays status and amount. An index on (customer_id, order_date DESC) with included status and amount can avoid both lookups and sorting. Deep history and broad exports may still favor scans or reporting storage.
Banking Transactions Use Case
A unique reference index supports one-transaction lookup, while an account-and-time index supports statements. Security predicates, exact ordering, reversals, hot accounts, and retention must be tested with realistic concurrency. Do not include sensitive payloads merely to make an index cover one query.
Testing Dashboard Use Case
A dashboard may filter by build, suite, environment, status, and start time. A few composite indexes should follow dominant drill-down paths. Event tables are write-heavy, so numerous wide covering indexes can make ingestion and retention expensive. Summaries may be better for long-range charts.
Login Authentication Use Case
A unique index on normalized username or email supports identity lookup and prevents duplicates. It should not store plaintext secrets. Password verification, rate limiting, account state, auditing, and constant-time cryptographic comparison remain application security responsibilities.
Clustered vs Nonclustered Index
| Characteristic | Clustered | Nonclustered |
|---|---|---|
| Leaf level | Complete SQL Server table rows | Keys, locators, and included columns |
| Count per table | One | Multiple supported |
| Role | Primary table organization | Secondary access path |
| Coverage | All table columns at leaf | Only stored key and included values |
| Lookup | Already reaches base row | May require Key or RID Lookup |
| Design impact | Locator for other indexes | Mainly affects its own queries and writes |
Common Mistakes
- Creating one index for every column without workload evidence.
- Ignoring leading-column order in composite indexes.
- Adding every SELECT column to make a giant covering index.
- Treating Key Lookups and RID Lookups as identical base structures.
- Assuming an index will always be used because it exists.
- Ignoring low selectivity, skew, and parameter sensitivity.
- Using functions or implicit conversions on search columns.
- Keeping overlapping indexes indefinitely.
- Rebuilding by fixed percentage without considering page count.
- Testing reads while ignoring insert, update, delete, and purge cost.
- Trusting missing-index suggestions without reviewing existing definitions.
- Applying SQL Server internals directly to other database engines.
Best Practices
- Start from an important measured query and its actual plan.
- Choose keys from equality, range, join, and ordering needs.
- Keep key width and column count as small as practical.
- Use included columns only for valuable coverage.
- Use filtered indexes for stable, selective subsets.
- Keep predicates sargable and data types compatible.
- Test common, rare, empty, and worst-case parameters.
- Benchmark read and write workloads under concurrency.
- Track usage, size, plans, statistics, density, and maintenance cost.
- Document each index's purpose, owner, supported queries, and rollback.
Interview Questions and Answers
How would you define a nonclustered index?
It is a separate ordered access structure containing key values, row locators, and optional included columns rather than the full base table at every leaf entry.
How many nonclustered indexes can a table have?
SQL Server supports multiple nonclustered indexes, subject to product limits, key-size rules, storage, and sensible workload cost.
What locator is stored on a clustered table?
The clustered-key columns identify the corresponding clustered row.
What locator is stored on a heap?
A row identifier points to the heap row, which can produce a RID Lookup when extra columns are required.
What is a covering index?
It contains all values needed by one query, allowing that query to avoid a separate base-row lookup.
What is the difference between keys and included columns?
Keys determine navigation and ordering; included columns exist at the leaf to provide output coverage.
Why might SQL Server ignore an index?
A scan may cost less when many rows qualify, the table is small, lookups are expensive, statistics predict low selectivity, or predicates do not match the key.
Why do indexes slow writes?
Each affected insert, update, and delete must maintain additional structures and generate related storage and log work.
Practical Exercises
- Create a city index and compare a selective seek with a broad scan.
- Observe a Key Lookup, then add included columns and compare reads.
- Repeat the lookup example on a heap and identify the RID Lookup.
- Reverse a composite key order and test which predicates remain seekable.
- Create a filtered index for a small active subset and test predicate matching.
- Compare one narrow index with one wide covering index during inserts.
- Inspect usage statistics across a representative workload window.
- Document whether each test index provides enough net value to retain.
Design Checklist
| Area | Review question |
|---|---|
| Workload | Which measured query and parameters need improvement? |
| Keys | Which equality, range, join, and order columns lead? |
| Locator | Is the base table clustered or a heap? |
| Coverage | Are lookups costly enough to justify included values? |
| Selectivity | How many rows qualify for common and skewed values? |
| Writes | What happens to inserts, updates, deletes, and loads? |
| Overlap | Does an existing index already provide the access path? |
| Evidence | Do actual plans and benchmarks show net benefit? |
Quick Reference
-- Single-column secondary access path
CREATE INDEX IX_employees_city
ON dbo.employees (city);
-- Composite covering index
CREATE INDEX IX_employees_department_salary
ON dbo.employees (department_id, salary)
INCLUDE (employee_name, email);
-- Unique alternate key
CREATE UNIQUE INDEX UX_employees_email
ON dbo.employees (email);
-- Maintain after measured analysis
ALTER INDEX IX_employees_department_salary
ON dbo.employees REORGANIZE;
-- Remove an obsolete index
DROP INDEX IX_employees_city
ON dbo.employees;
Key Takeaway
A nonclustered index gives SQL Server an additional ordered route to table rows. Its leaf entries contain keys, a clustered-key or heap RID locator, and optional included values. That design can make selective filters, joins, ranges, ordering, grouping, and alternate-key enforcement much cheaper, but base-row lookups can dominate when too many rows qualify.
Effective design starts with measured queries, deliberate composite order, narrow keys, selective filters, and restrained coverage. Validate actual plans and realistic parameters, then account for storage, writes, logging, backups, replicas, and maintenance. The goal is not to create the most indexes; it is to maintain the smallest set of secondary access paths that provides clear net value for the real workload.