IDENTITY, SEQUENCE, GUID Strategies, Ordered IDs, and Hot-Page Considerations

Learning outcomes

ServiceHub needs identifiers for work orders, public API requests, and legally significant invoice numbers. One engineer proposes IDENTITY for all three because it “always gives the next number.” Another wants random GUIDs everywhere because they are globally unique. A third asks for a SEQUENCE so multiple tables can share a range. Each mechanism solves a different problem, and none promises a gap-free business sequence.

1. IDENTITY is table-column generation, not a transactional counter

An IDENTITY(seed, increment) property belongs to one table column. SQL Server generates a value during insert. The property does not enforce uniqueness by itself; uniqueness normally comes from a PRIMARY KEY or UNIQUE constraint. Identity values can have gaps because failed/rolled-back inserts and internal caching do not promise reuse.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab04') IS NULL EXEC(N'CREATE SCHEMA lab04 AUTHORIZATION dbo;');GODROP TABLE IF EXISTS lab04.IdentityProbe;CREATE TABLE lab04.IdentityProbe(    id bigint IDENTITY(1000,1) NOT NULL CONSTRAINT PK_IdentityProbe PRIMARY KEY,    note varchar(40) NOT NULL);GOINSERT lab04.IdentityProbe(note) VALUES ('committed-1');BEGIN TRANSACTION;INSERT lab04.IdentityProbe(note) VALUES ('rolled-back');SELECT SCOPE_IDENTITY() AS allocated_inside_transaction;ROLLBACK;INSERT lab04.IdentityProbe(note) VALUES ('committed-2');GOSELECT * FROM lab04.IdentityProbe ORDER BY id;SELECT IDENT_CURRENT(N'lab04.IdentityProbe') AS current_identity_value;GO

You should see a missing identifier for the rolled-back insert. That is normal. SCOPE_IDENTITY() is generally safer than session/global identity functions when you need the value generated by your own insert scope, but the broader lesson is that generated numeric IDs are not a receipt ledger.

2. SEQUENCE is a schema object that can serve multiple consumers

A SEQUENCE is independent of a table. Code can request NEXT VALUE FOR before or during an insert, and several tables can use the same sequence. This flexibility is useful for coordinated technical numbering, but values are generated outside the transaction that consumes them. Microsoft explicitly documents that rollback does not give a sequence value back.

USE ServiceHubLab;GODROP SEQUENCE IF EXISTS lab04.DispatchSequence;GOCREATE SEQUENCE lab04.DispatchSequence    AS bigint    START WITH 50000    INCREMENT BY 1    CACHE 20;GOSELECT NEXT VALUE FOR lab04.DispatchSequence AS first_value;GOBEGIN TRANSACTION;SELECT NEXT VALUE FOR lab04.DispatchSequence AS rolled_back_value;ROLLBACK;GOSELECT NEXT VALUE FOR lab04.DispatchSequence AS next_after_rollback;GOSELECT name, current_value, cache_size, is_cachedFROM sys.sequencesWHERE object_id = OBJECT_ID(N'lab04.DispatchSequence');GO

The sequence advances regardless of transaction outcome. With CACHE, an unexpected shutdown can also lose unused cached numbers. NO CACHE persists each current value more often but still cannot make sequence usage gap-free because applications can request values and never commit or use them.

3. GUIDs trade compactness and locality for decentralized uniqueness

uniqueidentifier stores a 16-byte GUID. NEWID() generates values without an increasing key pattern, which is convenient for distributed generation but can create random insertion patterns when used as the leading clustered key. Wider clustering keys also propagate into nonclustered index row locators, increasing storage. These costs do not make GUIDs “bad”; they mean physical design must match the workload.

NEWSEQUENTIALID() can reduce random leaf-level insert behavior when used as a DEFAULT on a uniqueidentifier column. Microsoft warns that its values are predictable enough to be unsuitable when predictability exposes sensitive resources, and sequential clusters can shift after restart/failover/movement. It is not a cryptographic secret or a portable monotonic clock.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab04.GuidProbe;CREATE TABLE lab04.GuidProbe(    guid_id uniqueidentifier NOT NULL        CONSTRAINT DF_GuidProbe_Id DEFAULT NEWSEQUENTIALID(),    external_token uniqueidentifier NOT NULL        CONSTRAINT DF_GuidProbe_Token DEFAULT NEWID(),    note varchar(40) NOT NULL,    CONSTRAINT PK_GuidProbe PRIMARY KEY CLUSTERED (guid_id));GOINSERT lab04.GuidProbe(note) VALUES ('one'),('two'),('three');SELECT guid_id, external_token, noteFROM lab04.GuidProbeORDER BY guid_id;GO

This tiny lab cannot prove fragmentation or throughput. Those are workload properties that require enough rows, concurrency, cache context, and storage observations. The lesson intentionally avoids fake performance numbers.

4. Sequential keys can create a hot last page under concurrency

Ascending identities and other ever-increasing clustered keys usually insert at the right edge of a B+ tree. Under high concurrent insert rates, many workers can contend for the same final page, often visible as PAGELATCH_EX waits. SQL Server 2019 and later provides OPTIMIZE_FOR_SEQUENTIAL_KEY, an index option designed for last-page insert contention.

Do not enable it merely because a table has an IDENTITY column. First verify that the clustered/index leading key is sequential, the workload is highly concurrent, and latch evidence points to last-page contention. The option changes throughput/fairness behavior; it is not a universal “identity performance switch.”

USE ServiceHubLab;GOSELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,       OBJECT_NAME(i.object_id) AS table_name,       i.name AS index_name,       i.optimize_for_sequential_keyFROM sys.indexes AS iWHERE i.object_id IN (OBJECT_ID(N'ops.WorkOrder'), OBJECT_ID(N'lab04.IdentityProbe'))  AND i.index_id > 0;GO

A zero here proves only that the option is currently off. It does not prove contention exists or does not exist. Later observability/performance chapters use waits, latch evidence, workload concurrency, and measured throughput to make that decision.

5. Deliberately wrong approach: promise gap-free invoice numbers with IDENTITY

A billing table uses IDENTITY as the legal invoice number and a report flags any missing number as fraud. A transaction rolls back after identity allocation, producing a gap. The database behaved correctly; the design attached a business guarantee to a mechanism that never promised it.

6. Hands-on lab: compare allocation semantics

USE ServiceHubLab;GOSELECT    OBJECT_SCHEMA_NAME(ic.object_id) AS schema_name,    OBJECT_NAME(ic.object_id) AS table_name,    c.name AS identity_column,    ic.seed_value,    ic.increment_value,    ic.last_valueFROM sys.identity_columns AS icJOIN sys.columns AS c  ON c.object_id = ic.object_id AND c.column_id = ic.column_idWHERE ic.object_id = OBJECT_ID(N'lab04.IdentityProbe');SELECT name, start_value, increment, current_value, cache_size, is_cachedFROM sys.sequencesWHERE object_id = OBJECT_ID(N'lab04.DispatchSequence');GO

Verification checklist

• You observed an IDENTITY value not reused after rollback.
• You observed a SEQUENCE value consumed outside transaction rollback.
• You can explain why CACHE and NO CACHE do not create a gap-free guarantee.
• You can explain the locality/width/predictability tradeoffs of GUID strategies.
• You did not infer last-page contention from key shape alone; you treated it as a measured concurrency problem.

USE ServiceHubLab;GODROP TABLE IF EXISTS lab04.GuidProbe;DROP TABLE IF EXISTS lab04.IdentityProbe;DROP SEQUENCE IF EXISTS lab04.DispatchSequence;GO

7. Production judgment and next bridge

Choose key generation based on scope, distribution, storage, concurrency, exposure, and business semantics. Small increasing numeric keys are compact and cache-friendly but can concentrate inserts. Random GUIDs decentralize generation but widen indexes and randomize placement. Sequential GUIDs improve locality but are not private unpredictable tokens. Sequences coordinate multiple consumers but are intentionally decoupled from transaction rollback. An additional operational concern is recovery and replay: after restores, failovers, retries, or message redelivery, application logic must rely on enforced uniqueness/idempotency rules rather than assuming a generated value reveals commit order or business chronology. Identity/sequence metadata is allocation state, not a durable event ledger.

Lesson 4 keeps the schema doing useful work without duplicating expressions in every query. Computed columns can centralize deterministic transformations and can even become indexed access paths—but only when determinism, precision, ownership, data types, and SET-option requirements are satisfied.

Authoritative references

• CREATE SEQUENCE — sequence object semantics, cache and rollback behavior
• Sequence numbers — limitations, uniqueness and gap behavior
• NEWSEQUENTIALID — sequential GUID behavior and privacy/topology cautions
• CREATE INDEX — sequential-key contention and OPTIMIZE_FOR_SEQUENTIAL_KEY
• SQL Server 2025 build versions — current engine baseline