GUID Keys: Separate Public Identity from Clustering

A globally unique identifier can be the right public identifier without being the best clustered key. Those decisions solve different problems. Public identity supports integration and distribution; clustering influences row locality, index size, and insertion behavior inside one SQL Server table.

Understand the cost being measured

A uniqueidentifier occupies sixteen bytes, compared with eight for bigint. In a clustered rowstore table, the clustered key also serves as the row locator in nonclustered indexes. A wide clustered key can therefore increase storage beyond the base table. The total effect depends on index definitions, compression, and row shape, not just one column's declared size.

Random NEWID values distribute inserts across the key space. When a destination page lacks space, additional page work and splits can occur. This is different from the concentrated last-page contention possible with an increasing key. One design distributes activity while the other concentrates it; neither is automatically optimal for every concurrency pattern.

The following practice schema separates a narrow internal key from a unique public GUID. Run it in a disposable database. It is a design option to benchmark, not a command to migrate every existing table.

CREATE TABLE dbo.KeyDesignDemo
( InternalId bigint IDENTITY(1,1) NOT NULL
    CONSTRAINT PK_KeyDesignDemo PRIMARY KEY CLUSTERED,
  PublicId uniqueidentifier NOT NULL DEFAULT NEWID(),
  CreatedAt datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
  Payload nvarchar(200) NOT NULL,
  CONSTRAINT UQ_KeyDesignDemo_PublicId UNIQUE NONCLUSTERED(PublicId)
);
INSERT dbo.KeyDesignDemo(Payload) VALUES(N'example');
SELECT InternalId,PublicId FROM dbo.KeyDesignDemo;

A lookup by PublicId uses its unique nonclustered index and may need another lookup for Payload. Returning only InternalId can avoid that extra payload access. Include columns only when the read benefit justifies wider indexes and additional write work.

Sequential GUIDs solve only part of the problem

NEWSEQUENTIALID can reduce random insertion behavior when used as a uniqueidentifier column default. It is not a generic scalar replacement usable everywhere NEWID appears, and its ordering behavior across restarts or machine changes should not be treated as a business clock. The identifier also should not be used as an authorization secret.

Sequential insertion does not make a sixteen-byte key narrower. It can also move pressure toward a hot insertion area. When diagnosing that pattern, distinguish page-latch waits from transaction-lock blocking and storage I/O waits. A change aimed at fragmentation will not necessarily fix last-page contention.

Lower fill factor reserves space during index creation or rebuild; it does not permanently keep that percentage free. It can reduce some splits while increasing page count and read footprint. Choose it from measured growth patterns rather than applying a low value to every index as a standing policy.

Compare the complete workload

Benchmark representative insert concurrency, public-ID lookups, internal joins, and range reports. Measure total index size, log generation, page activity, throughput, and tail latency. A design that improves insertion speed while doubling common lookup work may not improve the application overall.

Changing an existing clustered key is a structural migration. Inventory foreign keys, dependent nonclustered indexes, replication requirements, and application assumptions. Adding InternalId does not automatically redirect relationships that still use the public GUID. Decide whether children retain GUID references or move to internal keys, and plan the mapping explicitly.

For distributed writers, consider where IDs are generated and how data merges. A local identity is not globally unique by itself. Keeping a globally unique external identifier can preserve the integration contract while the internal key remains local to the database.

Test failover, bulk loads, deletes followed by inserts, and a dataset larger than the buffer pool. Keep security checks based on user permissions even when identifiers are hard to guess. The correct conclusion is a workload-backed key strategy, not a rule that GUIDs are always bad or sequential values always fast.

Technical references: Microsoft Learn: Index design · Microsoft Learn: NEWSEQUENTIALID.