Narrow Index Keys: Why the Clustered Key Width Matters Everywhere

One wide identifier keeps appearing in indexes that never search for it. Choosing narrow index keys reduces that repeated storage burden. The clustered row locator explains why the choice travels beyond one index.

Follow the Clustered Row Locator

I inspect the clustered key before counting redundant nonclustered indexes. That key identifies rows in a clustered table. Nonclustered indexes need a locator to reach those rows.

A wide clustered key therefore carries costs beyond the clustered structure. Its columns become part of nonclustered index storage where required. An integer key uses less space than a uniqueidentifier key alone.

The precise placement depends on index uniqueness and existing key columns. SQL Server doesn't duplicate an already present key column unnecessarily. Avoid treating every index as an identical storage formula.

A nonunique clustered index also needs an internal uniqueifier for duplicate keys. That extra detail affects row identification. A narrow nonunique key isn't the same design as a narrow unique key.

The point is to inspect the full table design. Clustered width is one input among access order and write behavior. It deserves attention because its effects are repeated.

Build Comparable Tables on a Copy

The following tables share an external GUID identifier and business columns. One clusters on the GUID. The other clusters on an integer while keeping GUID uniqueness through a separate index.

That separate unique index isn't free. Include it in the comparison. Removing a business uniqueness requirement would make the test unfair.

Run the setup in a disposable database. Generate sample inputs once and insert them into both tables. The sample size isn't an observed production result.

The integer key is supplied explicitly so rows correspond across designs. A real application can choose IDENTITY or another allocation method. That allocation decision also needs an integration review.

Each design has the same secondary search index. It lets you inspect how its locator differs. The included amount column keeps this example's reporting lookup comparable.

CREATE TABLE #KeyInputs
(ItemId int PRIMARY KEY, ExternalId uniqueidentifier NOT NULL, RegionId int, Amount decimal(12,2));
INSERT #KeyInputs
SELECT TOP (3000) ROW_NUMBER() OVER (ORDER BY a.object_id, b.object_id),
       NEWID(), ABS(CHECKSUM(NEWID())) % 10, 10.00
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE TABLE dbo.WideKeyDemo
(ExternalId uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
 ItemId int NOT NULL, RegionId int NOT NULL, Amount decimal(12,2) NOT NULL);
CREATE TABLE dbo.NarrowKeyDemo
(ItemId int NOT NULL PRIMARY KEY CLUSTERED,
 ExternalId uniqueidentifier NOT NULL UNIQUE NONCLUSTERED,
 RegionId int NOT NULL, Amount decimal(12,2) NOT NULL);
INSERT dbo.WideKeyDemo SELECT ExternalId, ItemId, RegionId, Amount FROM #KeyInputs;
INSERT dbo.NarrowKeyDemo SELECT ItemId, ExternalId, RegionId, Amount FROM #KeyInputs;
CREATE INDEX IX_WideKeyDemo_Region ON dbo.WideKeyDemo(RegionId) INCLUDE(Amount);
CREATE INDEX IX_NarrowKeyDemo_Region ON dbo.NarrowKeyDemo(RegionId) INCLUDE(Amount);

Measure What Narrow Index Keys Save on Every Index

sys.dm_db_partition_stats reports allocated and used pages by partition. Join it to sys.indexes for readable names. Sum partitions when comparing a partitioned design as one index.

A database page is eight kilobytes. The query converts page counts into kilobytes for inspection. It supplies measurements from your execution rather than invented savings.

Look at both the secondary reporting index and the complete table design. The extra GUID uniqueness index in the narrow design belongs in that total. A favorable single-index result isn't the whole capacity story.

Reserved pages and used pages answer different questions. Reserved pages include allocated space not currently used for data structures. Keep the same metric on both sides of the comparison.

On my small test run, the reporting index on the integer design used fewer pages than its GUID twin. The narrow table as a whole still used more pages, because the extra unique GUID index cost more than that saving. That is exactly why the total belongs in the review.

Narrow index keys become convincing when the repeated locator cost is visible. Repeat the test with representative widths and indexes. A tiny sample doesn't predict a large table's page layout precisely.

SELECT OBJECT_NAME(p.object_id) AS TableName, i.name AS IndexName,
       SUM(p.row_count) AS IndexRows,
       SUM(p.used_page_count) * 8 AS UsedKB,
       SUM(p.reserved_page_count) * 8 AS ReservedKB
FROM sys.dm_db_partition_stats AS p
JOIN sys.indexes AS i ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.WideKeyDemo'), OBJECT_ID(N'dbo.NarrowKeyDemo'))
GROUP BY p.object_id, i.name
ORDER BY TableName, IndexName;

Connect Page Width to Reads and Memory

Wider records leave room for fewer records per page. More pages can mean more logical reads for comparable access. More retained pages also consume buffer pool space.

Those are mechanisms, not a promised measurement for every query. A selective covering query and a broad scan use indexes differently. Test the actual workload before claiming a read reduction.

Enable the actual execution plan and STATISTICS IO for the following queries. Both return the same grouped business result from the same sample inputs. Compare reads and access paths beside that correctness check.

A small table can produce matching reads despite different key widths. That doesn't invalidate the storage mechanism. It means your sample isn't evidence of a measurable read benefit at that scale. In my run, the integer design did read fewer pages for the same ten grouped rows.

I include the largest nonclustered indexes in the production review. Their width repeats across the most pages. A few bytes deserve attention when they travel everywhere.

SET STATISTICS IO ON;
SELECT RegionId, SUM(Amount) AS TotalAmount FROM dbo.WideKeyDemo GROUP BY RegionId;
SELECT RegionId, SUM(Amount) AS TotalAmount FROM dbo.NarrowKeyDemo GROUP BY RegionId;
SET STATISTICS IO OFF;

Don't Confuse Width With Insert Order

A random GUID clustered key also changes insertion locality. Page splits and fragmentation are separate from the width itself. A sequential GUID improves locality without becoming a four-byte integer.

Likewise, a rising integer key concentrates inserts at the right edge. Under heavy concurrency, that can create contention. Narrower doesn't mean every write workload becomes faster.

Keep those variables separate during testing. Changing key type and insertion order together tests more than width. Explain which mechanism each observation supports.

Composite natural keys add another consideration. Their business meaning is useful, but changing values can propagate through related structures. A stable surrogate can simplify that operational contract.

The surrogate doesn't replace natural uniqueness automatically. Keep a unique constraint or index for the business key. Otherwise the storage change quietly weakens the model.

Skip Narrow Index Keys When Access Needs a Wider One

A clustered order aligned with important range searches can justify a wider key. A partitioned design can impose additional key requirements. Review those requirements before proposing a universal integer replacement.

What query benefits from the existing order? Answer that using its actual plan and access pattern. A replacement key can save locator width while losing valuable locality.

Changing a clustered key also rebuilds affected nonclustered structures. Test the deployment cost on a representative copy. Include log usage, blocking and foreign key changes in the plan.

I prefer narrow index keys when they preserve the necessary access and uniqueness rules. I keep a wider design when its measured workload benefit justifies the cost. The smallest key doesn't win by being small alone.

Record both the repeated storage cost and the access benefit. That gives the team a concrete decision. An identifier shouldn't need its own moving truck in every index.

Related reading on this blog: Using NEWID vs NEWSEQUENTIALID for Performance and Index Key Size Limits: 900 and 1,700 Bytes Explained.

A clustered key is not confined to one index, it is a row locator carried through the table's access structures.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.