Learn the key differences between clustered and nonclustered indexes in SQL. Visual diagrams, performance comparisons, and practical examples for better database design.
Jump into the SQLNoir case files and put these tips to work.
Indexes are the secret weapon for fast SQL queries. But choosing the wrong type can make your database slower, not faster. Let's break down clustered vs nonclustered indexes with visual examples so you'll know exactly when to use each.
Here's the TL;DR:
| Feature | Clustered Index | Nonclustered Index |
|---|---|---|
| Storage | Data stored in index order | Separate structure with pointers |
| Quantity | One per table | Up to 999 per table |
| Best for | Range queries, full scans | Selective point lookups |
| Speed | Fastest for primary key lookups | Faster for filtered queries on non-key columns |
Quick reference comparison
A clustered index determines the physical order of data in a table. The rows ARE stored in the order of the clustered index key. Think of a phone book: the entries are physically sorted by last name, so finding “Smith” means jumping to the S section.
Key characteristics of clustered indexes:
In a clustered index, leaf nodes contain the actual table data, sorted by the index key.
Here's how you create a clustered index:
-- Primary key automatically creates clustered index in SQL Server CREATE TABLE suspects ( suspect_id INT PRIMARY KEY, -- Clustered by default name VARCHAR(100), last_seen DATE ); -- Or explicitly create a clustered index CREATE CLUSTERED INDEX idx_suspects_id ON suspects(suspect_id);
In SQL Server, PRIMARY KEY creates a clustered index unless you specify NONCLUSTERED.
A nonclustered index is a separate data structure that lives outside the table. It contains the indexed column values plus a pointer back to the actual row. This pointer is called a “bookmark” or “row locator.”
Key characteristics of nonclustered indexes:
Nonclustered leaf nodes contain the index key plus a pointer back to the actual row.
Here's how you create a nonclustered index:
-- Simple nonclustered index CREATE NONCLUSTERED INDEX idx_suspects_name ON suspects(name); -- Covering index with INCLUDE clause CREATE NONCLUSTERED INDEX idx_suspects_name_covering ON suspects(name) INCLUDE (last_seen, age); -- Now queries selecting only name, last_seen, age -- don't need a bookmark lookup!
The INCLUDE clause adds columns to the leaf level without sorting by them.
Understanding indexes is crucial for writing efficient SQL. If you want to practice querying databases where every millisecond counts, SQLNoir's detective cases challenge you to solve mysteries with real SQL queries.
The fundamental difference comes down to what's stored in the leaf nodes:
This single difference explains ALL performance characteristics of each index type.
| Aspect | Clustered Index | Nonclustered Index |
|---|---|---|
| Leaf nodes contain | Actual data rows | Index key + pointer |
| Physical data order | Determined by index | Not affected |
| Number per table | Maximum 1 | Up to 999 |
| Storage overhead | Lower (data is the index) | Higher (separate structure) |
| Range scan performance | Excellent (sequential) | Requires bookmark lookups |
| Point lookup performance | Excellent (direct path) | Good (with covering index: excellent) |
| Insert/Update cost | Higher if reordering needed | Additional index maintenance |
| When to use | Primary key, range-scanned columns | Frequently filtered non-key columns |
| Auto-created by | PRIMARY KEY (SQL Server) | UNIQUE constraint (as option) |
| PostgreSQL behavior | CLUSTER command (one-time reorder) | Default for CREATE INDEX |
Complete 10-point comparison of clustered vs nonclustered indexes
Clustered indexes shine in specific scenarios:
SELECT *FROM casesWHERE case_date BETWEEN '2024-01-01' AND '2024-12-31'ORDER BY case_date;This query benefits massively from a clustered index on case_date
-- If most queries filter/sort by date, cluster on date CREATE TABLE cases ( case_id INT, case_date DATE, title VARCHAR(200), status VARCHAR(50) ); CREATE CLUSTERED INDEX idx_cases_date ON cases(case_date); -- Now range queries are lightning fast: SELECT * FROM cases WHERE case_date BETWEEN '2024-01-01' AND '2024-06-30'; -- Data is physically ordered by date!
Nonclustered indexes are your go-to for:
SELECT * FROM suspects WHERE last_name = 'Martinez'; -- Without index: Full Table Scan -- Scans ALL 1,000,000 rows -- 5,000+ page reads -- ~2.5 seconds
•Examines every row in the table
•Slow even for one matching record
CREATE NONCLUSTERED INDEX idx_lastname ON suspects(last_name); SELECT * FROM suspects WHERE last_name = 'Martinez'; -- With index: Index Seek + Bookmark Lookup -- ~10 page reads -- ~0.01 seconds
•Jumps directly to matching entries
•100x+ faster for selective queries
Nonclustered index dramatically improves selective lookup performance
Think you understand when to use each index type? Test your skills by solving a case where choosing the right query approach is the difference between cracking the case and hitting a dead end.
Start InvestigationFollow this decision tree when choosing your index type:
PRIMARY KEY → Clustered (auto). Range scans + no existing clustered → Consider clustered. Already have clustered + frequent WHERE → Nonclustered. Need full row without bookmark lookup → Covering nonclustered.
Q1.You have an 'orders' table and frequently query orders by customer_id (not the primary key). Which index type?
Q2.A query retrieves only customer_id and order_date from a million-row table. What's the fastest approach?
Q3.In SQL Server, what happens when you define a PRIMARY KEY on a table?
Q4.Why can a table have only ONE clustered index?
When you cluster on a column that changes often, every update may require physically moving the row to maintain sort order. This causes “page splits” and degrades write performance.
-- Status changes frequently: Open → In Progress → Closed CREATE CLUSTERED INDEX idx_status ON cases(status); -- Every status update may require row movement UPDATE cases SET status = 'Closed' WHERE case_id = 123; -- Row physically moves from 'In Progress' section to 'Closed' section!
•Every status change may require physical row movement
•Causes page splits and fragmentation
•Write performance degrades over time
-- case_id is auto-increment, never changes -- PRIMARY KEY creates clustered index automatically CREATE TABLE cases ( case_id INT PRIMARY KEY, status VARCHAR(20) ); -- Status queries use nonclustered index CREATE NONCLUSTERED INDEX idx_status ON cases(status); -- Status updates don't move rows!
•New rows always go to the end (no reordering)
•Status updates don't move physical rows
•Both query patterns are fast
Avoid clustering on columns that change frequently
Every nonclustered index must be updated on INSERT, UPDATE, and DELETE operations. Having too many indexes slows down writes.
-- Each index speeds up reads but slows down writes CREATE NONCLUSTERED INDEX idx_1 ON suspects(name); CREATE NONCLUSTERED INDEX idx_2 ON suspects(age); CREATE NONCLUSTERED INDEX idx_3 ON suspects(last_seen); CREATE NONCLUSTERED INDEX idx_4 ON suspects(city); CREATE NONCLUSTERED INDEX idx_5 ON suspects(status); -- Every INSERT now updates 5 indexes + the table! INSERT INTO suspects VALUES (...); -- Every UPDATE potentially updates multiple indexes UPDATE suspects SET status = 'Cleared' WHERE suspect_id = 123;
Rule of thumb: Index columns that are frequently in WHERE clauses, not every column.
If your queries consistently need specific columns, a covering index eliminates bookmark lookups entirely:
-- Without covering index: Index Seek + Bookmark Lookup CREATE NONCLUSTERED INDEX idx_name ON suspects(last_name); SELECT last_name, first_name, phone FROM suspects WHERE last_name = 'Smith'; -- Has to look up the actual row for first_name and phone -- With covering index: Index Seek only! CREATE NONCLUSTERED INDEX idx_name_covering ON suspects(last_name) INCLUDE (first_name, phone); SELECT last_name, first_name, phone FROM suspects WHERE last_name = 'Smith'; -- All columns in the index, no bookmark lookup needed!
These are the index questions that come up in technical interviews:
A: Clustered indexes determine the PHYSICAL order of data on disk. The leaf nodes contain the actual data rows. Nonclustered indexes are separate structures where leaf nodes contain index keys + pointers back to the actual rows. This is why you can have only one clustered index (data can only be sorted one way) but many nonclustered indexes.
A: Because data can only be physically sorted one way. You can't alphabetize a phone book by both name AND address simultaneously. The clustered index key determines row order on disk.
A: A covering index includes all columns needed by a query, either in the key columns or via INCLUDE clause. This eliminates the need for a bookmark lookup because the query can be satisfied entirely from the index.
A: Page splits occur when a new row needs to be inserted into a full data page in a clustered index. The database must split the page in two and redistribute rows. This causes fragmentation and slows writes. Choose clustered index keys that grow sequentially (like auto-increment IDs) to minimize page splits.
A: When you have a covering index that includes all columns needed by the query. The nonclustered index is smaller than the full table, so scanning it is faster. Also, for highly selective queries that return few rows, a nonclustered index seek + bookmark lookup can be faster than a clustered range scan that returns more data than needed.
Ready to prove your SQL knowledge?
SQLNoir challenges you to use efficient queries to solve real detective cases. No index? Good luck scanning a million rows to find your suspect.
Start Your Investigation →Yes! A table can have ONE clustered index (typically on primary key) and up to 999 nonclustered indexes on other columns. This is the most common setup.
The table becomes a “heap.” Rows are stored in no particular order. Heaps can work for certain write-heavy scenarios but generally perform worse for reads. Most tables benefit from having a clustered index.
PostgreSQL handles it differently. You can CLUSTER a table to physically reorder it by an index, but unlike SQL Server, this is a one-time operation. New inserts don't maintain the order. PostgreSQL's default CREATE INDEX creates nonclustered indexes.
Check the execution plan. In PostgreSQL, use EXPLAIN or EXPLAIN ANALYZE. In SQL Server, view the execution plan. Look for “Index Seek” (good) vs “Table Scan” or “Clustered Index Scan” (no index used effectively).
No! Each index slows down writes (INSERT, UPDATE, DELETE). Index columns that are frequently filtered or sorted, but balance read performance gains against write overhead. Monitor query patterns and index strategically.