Clustered vs Non-Clustered
Overview
If a database table has 10 million rows, and you run SELECT * WHERE email = 'x@y.com', the database must physically scan all 10 million rows (a Sequential/Full Table Scan), which takes seconds. An Index is a separate, highly optimized Data Structure (usually a B-Tree) that the database builds in the background. It allows the database to find 'x@y.com' in milliseconds (an Index Seek). There are two main types: Clustered (the actual table structure itself) and Non-Clustered (a separate lookup map).
Syntax
-- 1. The Clustered Index (Primary Key)
-- A table can only have EXACTLY ONE Clustered Index.
-- It physically dictates the sorted order of the data on the hard drive.
CREATE TABLE users (
id INT PRIMARY KEY, -- The physical table is sorted by this ID!
email VARCHAR(100)
);
-- 2. Non-Clustered Index (Secondary Index)
-- You can have MANY Non-Clustered Indexes!
-- It creates a separate B-Tree map pointing back to the physical rows.
CREATE INDEX idx_user_email ON users (email);
-- Finding a user by Email is now lightning fast!
SELECT * FROM users WHERE email = 'x@y.com';Common Pitfalls
- Over-indexing. Every time you run an
INSERT,UPDATE, orDELETE, the database must also update the physical B-Tree index. If you put an index on every single column, yourSELECTqueries will be incredibly fast, but yourINSERTqueries will become disastrously slow, causing write bottlenecks. Only index columns that are heavily used inWHERE,JOIN, orORDER BYclauses. - Indexing low-cardinality columns (like a 'Gender' or 'Is_Active' boolean column). If an index splits 10 million rows into two buckets (5M True, 5M False), the database optimizer will literally ignore the index because scanning it is slower than just scanning the physical table.
Interview Questions
Because a Clustered Index dictates the physical sort order of the raw data pages on the actual hard drive. A physical object cannot be sorted in two completely different ways simultaneously.
Real-World Example
Adding a unique index to ensure data integrity while simultaneously massively speeding up authentication queries.
-- The UNIQUE keyword automatically generates a B-Tree index under the hood!
CREATE UNIQUE INDEX idx_unique_active_email
ON users (email)
WHERE status = 'Active'; -- A Partial Index (Saves disk space!)Check Your Knowledge
Test your understanding of Clustered vs Non-Clustered with these quick questions.