Clustered Index in SQL
Introduction
A clustered index is the primary B+ tree organization of a table in Microsoft SQL Server. Its leaf level contains the table's complete data rows, ordered logically by the clustered key. Instead of maintaining one structure for the index and a separate heap for the rows, SQL Server stores the rows as the leaf records of that index. This arrangement can make selective lookups, ordered ranges, joins, and scans efficient when the key matches important access patterns.
The word clustered is easy to misunderstand. It does not promise that rows occupy one permanently contiguous disk region, and it does not guarantee query output order. Pages split, move, and become fragmented as data changes. The key order is maintained logically through the B+ tree, while an ORDER BY clause remains necessary whenever result order matters.
Clustered behavior is also database-specific. This tutorial uses SQL Server syntax for creation and maintenance, then compares that model with MySQL InnoDB, PostgreSQL, Oracle, and SQLite. Keeping those implementations separate is essential because a correct SQL Server statement can be invalid or mean something different elsewhere.
What Is a Clustered Index?
In SQL Server, a clustered index is an index whose leaf records are the actual table rows. Internal pages contain key ranges and child-page pointers that guide navigation. Because the complete row appears at the leaf, reaching the matching leaf record normally reaches the requested base data without a separate heap lookup.
A table with no clustered index is called a heap. A heap stores rows without clustered-key organization and lets nonclustered indexes locate them through row identifiers. A clustered table instead lets nonclustered indexes use the clustered key as the row locator. That difference makes clustered-key design affect the entire table, not only queries that mention the clustered column.
Why Clustered Indexes Matter
Large tables need efficient ways to locate a small row set. If an employee table contains ten million rows, searching one stable employee identifier should not require reading every page. A clustered seek can navigate from the root through intermediate pages to the relevant leaf page in a small number of steps.
Ordered leaf keys also help range access. A transaction-history query can find the first qualifying account and date, then read adjacent entries until the range ends. This is usually more efficient than repeatedly jumping among unrelated pages. The benefit is strongest when the selected key reflects dominant filters, joins, and range patterns.
B+ Tree Structure
A SQL Server clustered index is organized as a balanced B+ tree. The root page identifies broad key ranges. Zero or more intermediate levels narrow the range, and the leaf level holds the complete table rows. All leaf pages are linked in key sequence, allowing an ordered scan to continue from one page to the next.
Balanced does not mean every page is full or physically adjacent. It means leaf records remain at the same logical depth, keeping navigation predictable. As rows are inserted and deleted, SQL Server adjusts pages and may split them while preserving searchable key order.
Sample Employee Table
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,
hired_at DATE NOT NULL
);
At first, this table is a heap because it has neither a primary-key constraint nor a clustered index. The examples deliberately begin with that state so the clustered structure and primary-key decision can be examined separately.
Creating a Clustered Index
CREATE UNIQUE CLUSTERED INDEX CIX_employees_employee_id
ON dbo.employees (employee_id);
This statement creates a unique clustered index and reorganizes the table into the index structure. The index key is employee_id; all other columns remain in each complete leaf row even though they are not key columns. The operation can require substantial time, temporary space, logging, and I/O on a large table.
SQL Server allows only one clustered index per table. If one already exists, it must be dropped or changed through an appropriate migration before another clustered organization can be created.
Logical Order Is Not Output Order
SELECT employee_id, employee_name
FROM dbo.employees
ORDER BY employee_id;
The clustered key provides a logical order the optimizer may exploit, but only ORDER BY establishes the query result order. A parallel plan, allocation-order scan, join, aggregation, or later plan change can produce a different sequence when no ordering clause exists. Applications must never rely on an apparent natural order.
Why Only One Clustered Index Is Allowed
The leaf level of the clustered index is the table. One set of data rows cannot simultaneously be the leaf level of two independent B+ tree organizations. A table can therefore have one clustered index and many nonclustered indexes, subject to product limits and sensible workload design.
Additional search orders are represented by nonclustered indexes. They keep their own keys and point back to the clustered rows using the clustered key, which is why a wide or unstable clustered key increases the cost of every secondary access path.
Clustered Index and Primary Key
A primary key is a logical constraint: it identifies each row, disallows nulls, and enforces uniqueness. A clustered index is a physical access structure. SQL Server commonly implements a primary key as a unique clustered index when no clustered index already exists and the constraint does not specify otherwise, but that default does not make the concepts identical.
CREATE TABLE dbo.products (
product_id INT NOT NULL,
product_name VARCHAR(120) NOT NULL,
CONSTRAINT PK_products
PRIMARY KEY CLUSTERED (product_id)
);
Using a Nonclustered Primary Key
A table may need identity enforced by one key while clustering around another access pattern. SQL Server supports this explicitly:
CREATE TABLE dbo.transactions (
transaction_id BIGINT NOT NULL,
account_id BIGINT NOT NULL,
transaction_time DATETIME2 NOT NULL,
amount DECIMAL(14, 2) NOT NULL,
CONSTRAINT PK_transactions
PRIMARY KEY NONCLUSTERED (transaction_id)
);
CREATE CLUSTERED INDEX CIX_transactions_account_time
ON dbo.transactions (account_id, transaction_time, transaction_id);
The primary key still protects identity. The clustered key now groups each account's transactions in time order and adds transaction_id as a unique tiebreaker. This design should be chosen only after measuring the dominant workload and write behavior.
Unique and Nonunique Clustered Indexes
A unique clustered key maps every key value to exactly one row. If a clustered index is declared nonunique, SQL Server must still identify duplicate rows internally. It adds a hidden integer called a uniqueifier where duplicates require one. The uniqueifier can flow into nonclustered row locators and increase storage.
Uniqueness should reflect real data. Adding an explicit stable tiebreaker is often clearer than clustering on a heavily duplicated value alone. However, a needlessly wide composite key can be worse than accepting a small amount of duplicate overhead, so use measured cardinality and storage evidence.
Composite Clustered Index
CREATE UNIQUE CLUSTERED INDEX CIX_orders_customer_date_id
ON dbo.orders (customer_id, order_date, order_id);
A composite clustered index uses several columns in a defined sequence. This example supports one customer's orders over a date range and uses order_id to make the complete key unique. The leftmost order matters: it serves customer-led access well but is not equivalent to an index beginning with order_date.
Every component becomes part of nonclustered row locators. Composite clustered keys should therefore remain as narrow as the access requirement permits.
Choosing a Clustered Key
A strong SQL Server clustered key is commonly narrow, unique, stable, non-nullable, and steadily increasing. Narrow keys improve page density and reduce every nonclustered index locator. Uniqueness avoids hidden uniqueifiers. Stability prevents updates from relocating rows and changing secondary locators. Non-nullability gives each row a valid key.
Steadily increasing keys usually append near the logical end, limiting page splits and preserving locality. They can also concentrate concurrent inserts on the last page, creating latch contention at high throughput. Key choice is therefore a workload tradeoff, not a checklist that replaces testing.
Narrow Keys and Secondary Index Cost
Suppose a table has eight nonclustered indexes. A 32-byte clustered key may be repeated through all eight structures, while an 8-byte key uses far less space. The wider choice reduces entries per page, increases memory and I/O demand, and can deepen trees.
This multiplication is one reason long strings, mutable business descriptions, and unnecessarily large composite keys are poor clustered-key candidates. A compact surrogate key can be effective, while a separate unique constraint preserves the business identifier.
Stable Keys and Row Movement
Updating a clustered key is operationally similar to deleting the old index row and inserting a new one in the correct key location. Nonclustered locators may also need changes because they contain the clustered key. This creates more logging, locking, and page work than updating a non-key value.
Choose a key whose meaning does not change. Email addresses, phone numbers, status values, names, and organizational assignments are usually weak clustered keys because legitimate business events modify them.
Sequential and Random Keys
Integer identities and sequentially generated identifiers usually preserve insertion locality. Random GUID values distribute inserts through the tree, which can cause frequent page splits, low page density, larger indexes, and scattered I/O. Random distribution can reduce a last-page hotspot, but that benefit must be weighed against broader storage cost.
Do not select a random key merely to avoid one theoretical bottleneck. Measure latch waits, throughput, fragmentation, and cache effects first. SQL Server also offers product-specific options for sequential-key contention in supported versions.
Clustered Index Seek
SELECT employee_name, salary, city
FROM dbo.employees
WHERE employee_id = 103;
With the unique clustered index on employee_id, the optimizer can choose a Clustered Index Seek. It navigates to the relevant key and returns the complete leaf row. A seek describes how the access path is entered; it does not guarantee low cost if the predicate ultimately covers a very large range.
Clustered Index Scan
SELECT employee_id, employee_name, salary
FROM dbo.employees
WHERE salary > 0;
If nearly every row qualifies and no useful salary index exists, a Clustered Index Scan may be appropriate. The engine reads much or all of the clustered leaf level. Scans are not failures: reading the table sequentially can be cheaper than performing thousands of individual lookups.
Evaluate rows read, rows returned, logical reads, elapsed time, CPU, and concurrency. Replacing every scan is neither realistic nor desirable.
Clustered Index and WHERE
Predicates matching the leading clustered key can support efficient seeks. Equality on the first key followed by a range on the next often works especially well. A filter only on a nonleading key may scan a broad part of the structure unless another index is available.
SELECT transaction_id, transaction_time, amount
FROM dbo.transactions
WHERE account_id = 42001
AND transaction_time >= '2026-10-01'
AND transaction_time < '2026-11-01';
Clustered Index and ORDER BY
When predicates and requested order align with the clustered keys, SQL Server may avoid an explicit sort. The account-and-time index can produce one account's rows in timestamp order. Direction, equality predicates, joins, parallelism, and later operators determine whether the order remains usable.
Avoid adding a clustered index solely to remove one small sort. The clustered choice affects every write and nonclustered index, while an occasional in-memory sort may be inexpensive.
Clustered Index and Range Queries
Range access is a major clustered-index strength. The engine seeks to the lower boundary and continues through linked leaf pages until the upper boundary is reached. Date history, invoice sequences, ledger entries, and event timelines often benefit when their leading keys group the requested range.
Global clustering only by date may place unrelated customers together. A composite key such as (customer_id, order_date, order_id) better serves customer-specific history, while a date-led reporting workload may need a separate nonclustered index or a different table design.
Clustered Index and JOIN Operations
A clustered primary key is a useful target for foreign-key joins because each parent lookup reaches the base row. Depending on row counts and ordering, SQL Server may use nested loops with seeks, merge compatible ordered inputs, or choose a hash join.
The existence of a clustered index does not force one join algorithm. Cardinality estimates, available indexes on both sides, predicates, memory, statistics, and required output determine the plan.
Covering Behavior
The clustered index includes every table column at its leaf level, so it technically contains every value a query could request. That does not make it the smallest or cheapest access path. A narrow nonclustered index can answer a summary query with far fewer page reads than scanning wide clustered rows.
Coverage must be evaluated with page width and row count. The optimizer may seek a nonclustered key and use clustered lookups for a few rows, or scan a covering nonclustered index when many compact entries are needed.
Nonclustered Index Row Locators
On a clustered table, SQL Server nonclustered leaf entries use the clustered key to identify base rows. The clustered key is available within nonclustered indexes even when it was not explicitly listed. If the nonclustered key itself contains all clustered-key columns, SQL Server does not duplicate them unnecessarily.
On a heap, the locator is a row identifier based on physical placement. This architectural difference explains why converting a heap to a clustered table can require rebuilding existing nonclustered indexes: their locator representation changes.
Clustered vs Nonclustered Index
| Characteristic | Clustered index | Nonclustered index |
|---|---|---|
| Leaf level | Complete table rows | Index keys, locator, and included data |
| Count per table | One | Multiple supported |
| Purpose | Primary table organization | Additional access paths |
| Row locator | Not a separate base lookup | Clustered key or heap row identifier |
| Key impact | Affects all nonclustered indexes | Mostly affects its own structure |
Clustered Table vs Heap
A heap can be appropriate for staging, transient loading, or access patterns that do not need a stable clustered organization. Inserts may be simple, but forwarded records can appear when variable-length updates no longer fit on the original page, and nonclustered lookups use physical row identifiers.
A clustered table provides ordered key access and stable logical locators but pays B+ tree maintenance costs. Do not assume every heap is broken or every transactional table needs an identity-based clustered index. Compare actual loading, lookup, update, deletion, and maintenance behavior.
INSERT Behavior
Each inserted row must enter the correct leaf position. A steadily increasing key normally targets the right edge, preserving locality but potentially creating a hot final page under heavy concurrency. A random key distributes inserts but tends to split pages and reduce density.
Bulk loads, transaction log capacity, lock escalation, sort options, and existing nonclustered indexes influence total insertion cost. Benchmark with the complete production-like index set rather than an empty schema.
Page Splits and Fill Factor
When a target page lacks room for a new row, SQL Server may allocate another page and redistribute records. This page split generates logging and can increase logical fragmentation. Frequent splits often indicate random insertion, growing rows, an unsuitable key, or page density that does not match the workload.
Fill factor reserves free space during index build or rebuild. It is not a permanent guarantee because later changes consume that room. Setting it too low wastes cache and increases reads; setting it to 100 can be appropriate for append-oriented keys. Tune from measured split rates and read costs.
UPDATE Behavior
Updating non-key columns changes the clustered leaf row and every affected nonclustered included value. If the row grows beyond available space, additional page work may result. Updating the clustered key is more disruptive because the row must move to its new logical key location and secondary locators may change.
Wide mutable rows and broad updates can generate large logs and lock footprints. Batch carefully, preserve sargable predicates, and monitor replicas and recovery requirements.
DELETE Behavior
Deleting a clustered row also removes corresponding entries from nonclustered indexes. SQL Server may mark records as ghost entries for later cleanup, allowing the transaction to finish without immediately compacting every page. Large purges can still consume substantial logging, I/O, and lock resources.
Retention design may benefit from partitioning or batched deletion, but those choices depend on edition, schema, referential rules, and operational constraints.
Fragmentation
Logical fragmentation occurs when the next logical leaf page is not the next physical page, while low page density means pages contain excessive free space. Both can increase reads, but their importance depends on storage, cache, access pattern, and table size. A small index with high percentage fragmentation may have no meaningful performance issue.
Do not rebuild solely because a dashboard shows one percentage. Consider page count, density, workload, scan behavior, maintenance time, logging, and whether the observed query is actually slow.
Checking Fragmentation
SELECT
i.name,
ips.page_count,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(), OBJECT_ID('dbo.transactions'), NULL, NULL, 'SAMPLED'
) AS ips
JOIN sys.indexes AS i
ON i.object_id = ips.object_id
AND i.index_id = ips.index_id
WHERE i.index_id = 1;
Dynamic management function scans can themselves consume resources, especially in detailed mode. Run them with an appropriate scope and interpret results alongside workload evidence. The clustered index normally has index_id = 1; a heap uses zero.
Reorganize vs Rebuild
ALTER INDEX ... REORGANIZE incrementally compacts and reorders leaf pages online with lower resource spikes, but it does not recreate every level and does not update statistics with a full scan. ALTER INDEX ... REBUILD creates a fresh structure, can apply a new fill factor, and updates index statistics, but uses more CPU, I/O, log, temporary space, and potentially blocking.
ALTER INDEX CIX_transactions_account_time
ON dbo.transactions REORGANIZE;
ALTER INDEX CIX_transactions_account_time
ON dbo.transactions REBUILD
WITH (FILLFACTOR = 95);
Fixed rules such as reorganize above 5 percent and rebuild above 30 percent are starting points, not universal laws. Modern storage and workloads require evidence-based thresholds.
Online Maintenance
Supported SQL Server editions and versions provide online and resumable index operations with restrictions. Online does not mean resource-free or zero blocking; short schema locks and significant log, CPU, I/O, and replica impact can remain. Confirm feature support for the exact index and data types.
Plan disk space, transaction-log growth, maintenance duration, cancellation, failover, and rollback before operating on a large clustered index. Because the clustered leaf is the table, rebuilding it is a major table operation.
Dropping a Clustered Index
DROP INDEX CIX_employees_employee_id
ON dbo.employees;
Dropping the clustered index converts the table to a heap unless another operation replaces the structure. Existing nonclustered indexes may need their row locators rebuilt. If the clustered index supports a primary-key constraint, remove or alter the constraint through the correct migration instead of issuing an incompatible index drop.
Never make this change casually in production. Script recreation, check foreign keys and dependencies, estimate log and space, and test the workload after conversion.
Execution Plan Validation
SET STATISTICS IO, TIME ON;
SELECT transaction_id, transaction_time, amount
FROM dbo.transactions
WHERE account_id = 42001
AND transaction_time >= '2026-10-01'
AND transaction_time < '2026-11-01'
ORDER BY transaction_time, transaction_id;
Inspect the actual plan rather than predicting from index names. Review seek and residual predicates, estimated and actual rows, rows read, logical reads, lookups, sorts, spills, parallelism, memory grant, elapsed time, CPU, and waits. Test common, rare, empty, and worst-case parameter values because skew can change the best plan.
Creating a Clustered Index on an Existing Heap
Building a clustered index on a heap moves the table rows into the new B+ tree. If nonclustered indexes already exist, SQL Server may rebuild them so their heap row identifiers become clustered-key locators. Building the clustered structure first can therefore avoid duplicated work during a new table load.
For an established table, estimate outage or online behavior, log volume, destination and temporary space, backup effects, replicas, and recovery time. A syntactically simple statement can represent one of the largest schema changes in the database.
MySQL InnoDB Behavior
InnoDB uses the primary key as its clustered index. If no primary key exists, it chooses the first suitable unique non-null index; if none exists, InnoDB creates a hidden clustered index. Secondary index records contain the primary-key columns used to locate rows.
This makes a short primary key valuable in InnoDB as well, but SQL Server syntax such as CREATE CLUSTERED INDEX does not describe the MySQL interface. Define the primary key deliberately and assess how its width multiplies across secondary indexes.
PostgreSQL CLUSTER Is Different
PostgreSQL's CLUSTER command physically rewrites a table according to an existing index. It is a one-time operation: later inserts and updates are not continuously maintained in that clustered order. PostgreSQL remembers the selected index for future reclustering, but administrators must run the operation again when useful.
CREATE INDEX ix_events_account_time
ON events (account_id, event_time);
CLUSTER events USING ix_events_account_time;
Do not claim that PostgreSQL has the same always-maintained clustered leaf structure as SQL Server. Its heap and index architecture remains different after the rewrite.
Oracle and SQLite Differences
Oracle supports index-organized tables, where rows are stored in a B-tree organized by the primary key. That is conceptually related to clustered storage, but its syntax, logical row identifiers, overflow behavior, and maintenance are Oracle-specific. Oracle table clusters are another distinct feature and should not be equated casually with SQL Server clustered indexes.
SQLite generally stores ordinary rowid tables in a B-tree keyed by rowid, while WITHOUT ROWID tables use their primary key organization. SQLite does not support SQL Server's CREATE CLUSTERED INDEX statement. Design and explain each engine with its own documented model.
Cross-Database Comparison
| Database | Related behavior | Important distinction |
|---|---|---|
| SQL Server | Clustered index leaf is the table | One clustered index; explicit clustered syntax |
| MySQL InnoDB | Rows cluster around the primary key | Secondary indexes include primary-key columns |
| PostgreSQL | CLUSTER rewrites by an index | Order is not maintained after later changes |
| Oracle | Index-organized tables | Separate Oracle physical design feature |
| SQLite | Rowid or primary-key B-tree organization | No SQL Server clustered-index syntax |
E-Commerce Order History
An order system commonly retrieves one customer's recent orders. Clustering on (customer_id, order_date, order_id) places that range together logically and can satisfy filtering and ordering. The design may be poor for global date processing, however, because all customers' orders for one day are dispersed across customer ranges.
A separate date-led nonclustered index, partitioning strategy, reporting copy, or different clustered key may better serve operations staff. Prioritize the highest-value workload and measure total write amplification.
Banking Ledger
A ledger often reads transactions by account and posting sequence. A composite clustered key can keep one account's entries together and provide deterministic ordering. Immutability and uniqueness are critical because changing historical key values is expensive and can undermine audit semantics.
Hot accounts may concentrate activity within one range. Test concurrent posting, reconciliation, reversals, retention, and end-of-day processing rather than only an isolated SELECT.
Inventory System
An inventory table may be read primarily by warehouse and product. Clustering on those keys can support stock lookup and location reports, but a mutable warehouse assignment can force key changes. An immutable inventory-row ID may be operationally safer, with nonclustered indexes for location access.
The correct choice depends on whether the table models current state, append-only movements, or both. Separate current balances from immutable movement history when their access and retention patterns conflict.
Test Automation Results
A high-volume test-results table often appends records and reads recent results by run or build. A clustered key such as (run_id, result_id) groups one run, while a sequential identity alone optimizes ingestion. Dashboards filtering by project and execution time may need additional nonclustered indexes or summaries.
Measure ingestion bursts, retention deletes, report ranges, and row width. Large logs, screenshots, and payloads may belong in separate storage so the clustered leaf remains manageable.
Common Mistakes
- Assuming every primary key must be the clustered key.
- Using a wide string or large composite key without measuring secondary-index growth.
- Clustering on mutable values such as email, status, or department.
- Choosing a low-cardinality key and ignoring hidden uniqueifiers.
- Using random GUIDs without evaluating page splits and density.
- Assuming clustered order guarantees SELECT output order.
- Treating every clustered scan as a performance defect.
- Rebuilding indexes from fixed percentage rules without workload evidence.
- Ignoring log, temporary-space, replica, and blocking impact.
- Applying SQL Server terminology unchanged to PostgreSQL, MySQL, Oracle, or SQLite.
Clustered Index Best Practices
- Begin with measured filters, joins, ranges, ordering, and write patterns.
- Keep the clustered key narrow, stable, non-nullable, and preferably unique.
- Use an explicit tiebreaker when composite range keys require uniqueness.
- Estimate clustered-key bytes repeated through every nonclustered index.
- Test sequential-key contention as well as random-key page splits.
- Preserve
ORDER BYwhenever result sequence is required. - Validate actual plans, logical reads, CPU, latency, and waits.
- Measure insert, update, delete, bulk-load, and purge behavior.
- Maintain according to page count, density, fragmentation, and workload impact.
- Document the database-specific implementation and migration rollback.
Interview Questions and Answers
What is a clustered index in SQL Server?
It is the B+ tree structure whose leaf level contains the complete table rows, logically ordered by the clustered key.
Why can a table have only one clustered index?
The clustered leaf level is the table itself, so the same rows cannot be the leaf records of two independent primary organizations at once.
Is a primary key always clustered?
No. SQL Server often defaults a primary key to clustered when no clustered index exists, but a primary key may be explicitly nonclustered.
What does a nonclustered index use as its row locator?
On a clustered table it uses the clustered key; on a heap it uses a physical row identifier.
What happens when a clustered key is not unique?
SQL Server adds an internal uniqueifier where duplicate key values require one so every clustered row remains identifiable.
Does a clustered index guarantee row output order?
No. Only an explicit ORDER BY guarantees query result order.
What is the difference between a seek and a scan?
A seek navigates to a key or range, while a scan reads a broad portion or all of the leaf level. Either can be correct depending on selectivity and cost.
Is PostgreSQL CLUSTER equivalent to a SQL Server clustered index?
No. PostgreSQL physically reorders the table once; later modifications are not continuously maintained in that order.
Practical Design Checklist
| Area | Question to answer |
|---|---|
| Workload | Which important query patterns should the table organization serve? |
| Width | How many bytes will the key add to secondary indexes? |
| Uniqueness | Is uniqueness natural, explicit, and stable? |
| Writes | Where will inserts land, and can updates change the key? |
| Concurrency | Are page splits or last-page contention visible in waits? |
| Plans | Do actual plans show useful seeks, ranges, or ordered access? |
| Operations | Can logging, space, blocking, and rebuild time be supported? |
| Portability | Has behavior been confirmed for the deployed database engine? |
Hands-On Exercises
- Create a heap, load sample rows, and capture a lookup plan and logical reads.
- Add a unique clustered index and compare the lookup plan.
- Add a nonclustered index and inspect its clustered-key locator in metadata.
- Compare a clustered seek, a narrow range seek, and a full clustered scan.
- Insert sequential and random keys in test tables and compare page splits.
- Create a nonunique clustered index and inspect duplicate-key storage behavior.
- Measure fragmentation and density before and after a controlled rebuild.
- Explain why the same design cannot be copied directly to PostgreSQL.
Quick Reference
-- Create a unique clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_employees_employee_id
ON dbo.employees (employee_id);
-- Create a primary key that is not clustered
ALTER TABLE dbo.transactions
ADD CONSTRAINT PK_transactions
PRIMARY KEY NONCLUSTERED (transaction_id);
-- Reorganize or rebuild after measured analysis
ALTER INDEX CIX_transactions_account_time
ON dbo.transactions REORGANIZE;
ALTER INDEX CIX_transactions_account_time
ON dbo.transactions REBUILD;
-- Drop the clustered index and leave a heap
DROP INDEX CIX_employees_employee_id
ON dbo.employees;
Key Takeaway
A SQL Server clustered index is the table's primary B+ tree organization: its leaf level contains complete rows, and its key becomes the locator carried by nonclustered indexes. That makes the clustered key one of the most consequential physical design choices in a database. A narrow, stable, unique, non-nullable key often works well, but insertion pattern, range access, concurrency, and secondary-index cost must be tested together.
Use actual plans and production-like benchmarks rather than assumptions. Preserve explicit result ordering, monitor page density and fragmentation in context, and plan rebuilds as major table operations. Most importantly, keep vendor semantics precise: InnoDB clusters around its primary key, PostgreSQL CLUSTER performs a one-time reorder, and Oracle and SQLite use their own storage models. Good clustered-index design starts with the real engine and the real workload.