Clustered vs Nonclustered Design, Key Width, Selectivity, and Clustering-Key Propagation

Learning outcomes

ServiceHub has enough data and enough nonclustered indexes that a clustering-key choice now affects far more than one access path. In SQL Server rowstore, the clustered index leaf is the table, and its key is also carried as the row locator in every nonunique nonclustered index. A clustering key therefore changes table order, insert locality, page-split behavior, nonclustered index width, cache footprint, and lookup cost. This lesson turns that mechanism into a design process instead of the slogan “cluster on the most selective column.”

1. A clustered index is a storage decision, not a badge of importance

A clustered index orders the rowstore table by its clustering key. There can be only one because the leaf level contains the data rows themselves. A nonclustered index is a separate B+ tree whose leaf rows contain its own key columns, optional included columns, and a row locator back to the base object. If the base table is clustered, that locator is the clustering key; if it is a heap, the locator is a RID.

Good clustering candidates are usually narrow, stable, frequently useful for access/order, and practical for the table's insert pattern. Uniqueness is useful because a nonunique clustered key may require SQL Server to add an internal 4-byte uniquifier to duplicate key rows. High selectivity is relevant, but it is not enough: a 200-byte highly selective natural key can make every nonclustered index substantially wider.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab09') IS NULL EXEC(N'CREATE SCHEMA lab09 AUTHORIZATION dbo;');DROP TABLE IF EXISTS lab09.NarrowCluster;DROP TABLE IF EXISTS lab09.WideCluster;CREATE TABLE lab09.NarrowCluster(  work_order_id bigint IDENTITY(1,1) NOT NULL,  external_ref varchar(80) NOT NULL,  opened_at datetime2(3) NOT NULL,  status varchar(20) NOT NULL,  customer_id int NOT NULL,  CONSTRAINT PK_NarrowCluster PRIMARY KEY CLUSTERED(work_order_id),  CONSTRAINT UQ_NarrowCluster_external UNIQUE(external_ref));CREATE INDEX IX_Narrow_Status ON lab09.NarrowCluster(status);CREATE TABLE lab09.WideCluster(  external_ref varchar(80) NOT NULL,  region_code char(6) NOT NULL,  opened_at datetime2(3) NOT NULL,  status varchar(20) NOT NULL,  customer_id int NOT NULL,  CONSTRAINT PK_WideCluster PRIMARY KEY CLUSTERED(external_ref,region_code));CREATE INDEX IX_Wide_Status ON lab09.WideCluster(status);GO

2. Prove propagation from metadata

The clustered key is automatically present in nonclustered indexes even when you did not type it in the CREATE INDEX statement. sys.index_columns lets you inspect key and included columns, but remember that some automatically carried clustering-key columns can be represented differently in metadata than explicitly declared key columns. The reliable design conclusion is architectural: every nonclustered leaf row needs a base-row locator, and a clustered table's locator is the clustering key.

SELECT OBJECT_NAME(i.object_id) AS table_name,       i.name AS index_name,i.type_desc,       c.name AS column_name,ic.key_ordinal,ic.is_included_columnFROM sys.indexes AS iJOIN sys.index_columns AS ic  ON ic.object_id=i.object_id AND ic.index_id=i.index_idJOIN sys.columns AS c  ON c.object_id=ic.object_id AND c.column_id=ic.column_idWHERE i.object_id IN (OBJECT_ID(N'lab09.NarrowCluster'),OBJECT_ID(N'lab09.WideCluster'))ORDER BY table_name,i.index_id,ic.key_ordinal,ic.index_column_id;GOSELECT OBJECT_NAME(p.object_id) AS table_name,i.name,       SUM(ps.reserved_page_count)*8.0/1024 AS reserved_mb,       SUM(ps.used_page_count)*8.0/1024 AS used_mbFROM sys.dm_db_partition_stats AS psJOIN sys.partitions AS p  ON p.partition_id=ps.partition_idJOIN sys.indexes AS i  ON i.object_id=p.object_id AND i.index_id=p.index_idWHERE p.object_id IN (OBJECT_ID(N'lab09.NarrowCluster'),OBJECT_ID(N'lab09.WideCluster'))GROUP BY p.object_id,i.nameORDER BY table_name,i.name;GO

On an empty or tiny table, size differences are too small to be meaningful. That is expected. Populate a representative row count before drawing conclusions, and treat resulting megabytes as a local observation rather than a universal ratio.

3. Insert locality changes the failure mode

Random keys distribute inserts across existing key ranges and can drive middle-page splits. Monotonically increasing keys usually append at the right edge, reducing random fragmentation but concentrating concurrent insert activity on the last page. That can create PAGELATCH_EX contention under high concurrency. Chapter 08 introduced OPTIMIZE_FOR_SEQUENTIAL_KEY; here the design lesson is that an ever-increasing clustering key trades one risk for another. You still need workload evidence.

DECLARE @obj int=OBJECT_ID(N'lab09.NarrowCluster');SELECT i.name,ps.page_count,ps.avg_page_space_used_in_percent,       ps.avg_fragmentation_in_percentFROM sys.indexes AS iCROSS APPLY sys.dm_db_index_physical_stats (DB_ID(),@obj,i.index_id,NULL,'LIMITED') AS psWHERE i.object_id=@obj;SELECT i.name,os.leaf_insert_count,os.leaf_allocation_count,       os.page_latch_wait_count,os.page_latch_wait_in_msFROM sys.indexes AS iCROSS APPLY sys.dm_db_index_operational_stats (DB_ID(),i.object_id,i.index_id,NULL) AS osWHERE i.object_id=@obj;GO

Operational counters are cumulative and reset-sensitive. A large number is not a diagnosis unless you know the observation window and compare it with throughput, latency, waits, and a relevant baseline.

4. Wrong approach: “cluster on the most selective column”

Selectivity describes how well a predicate narrows rows. It matters for access paths, but clustering-key design also affects every nonclustered index, insert locality, update cost, and row stability. A GUID can be extremely selective yet create random insertion points; a long natural key can be selective yet multiply storage. Conversely, a moderately selective date key might support range scans but be a poor unique row locator.

5. Production judgment and cleanup

Changing a clustered index on a large production table is a major operation. It can rebuild the base table and nonclustered indexes, consume log and temp/storage, block or use online/resumable features depending on edition/version/options, and alter replication/HA operational characteristics. Record the engine build, edition, available maintenance window, and rollback method before treating a clustering-key redesign as “just an index change.”

DROP TABLE IF EXISTS lab09.WideCluster;DROP TABLE IF EXISTS lab09.NarrowCluster;GO

Authoritative references

• Index architecture and design guide — clustered/nonclustered structure and design guidance
• CREATE INDEX — current rowstore index syntax/options
• sys.dm_db_index_operational_stats — insert, allocation and latch counters
• SQL Server 2025 build versions — servicing baseline