Clustered vs Nonclustered Index
What is a clustered index vs a nonclustered index in relational databases?
Answers use simple, clear English.
Quick interview answer
A clustered index defines the physical row order (usually the primary key) — typically one per table. Nonclustered indexes are separate structures storing keys + pointers/row locators to the heap/clustered rows. Choose clustering for common range scans; avoid random GUIDs as clustered keys that cause fragmentation.
Detailed answer
A clustered index defines the physical row order (usually the primary key) — typically one per table. Nonclustered indexes are separate structures storing keys + pointers/row locators to the heap/clustered rows. Choose clustering for common range scans; avoid random GUIDs as clustered keys that cause fragmentation.
Real example & use case
Orders clustered on (customer_id, order_id) to make per-customer history range scans sequential.
Pros & cons
Pros of good clustering: fast range queries. Cons: wide/random clustered keys hurt inserts and secondary indexes.
Code example
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY, -- clustered by default in SQL Server
customer_id BIGINT NOT NULL,
total DECIMAL(12,2)
);
CREATE INDEX ix_orders_customer ON orders(customer_id);Practice code · sql (view only · no execution)
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY, -- clustered by default in SQL Server
customer_id BIGINT NOT NULL,
total DECIMAL(12,2)
);
CREATE INDEX ix_orders_customer ON orders(customer_id);