DEV Community: Damian Borowski

SEQUENCE vs IDENTITY in SQL Server: Choosing the Right Auto-Increment

Damian Borowski — Sat, 08 Aug 2026 13:05:59 +0000

TL;DR

Pick the wrong auto-increment strategy in SQL Server and you find out in production. An INT IDENTITY column hits 2,147,483,647. Replication nodes collide on the same keys. A failover jumps the counter by 10,000.

IDENTITY for simple single-table surrogate keys. SEQUENCE for anything that crosses tables, needs pre-allocation, or has to survive a migration. Neither gives you gapless numbering.

Scope decides everything. IDENTITY is table-bound. SEQUENCE is database-level.
Both produce gaps. Rollback, restart, cache flush. The number is gone.
CACHE is the performance lever on SEQUENCE. IDENTITY has no equivalent knob.
Portability? SEQUENCE follows ANSI SQL. PostgreSQL, Oracle, Db2. IDENTITY is SQL Server only.

The INT that ran out

A logistics system. Event-logging table, INT IDENTITY(1,1), high insert volume. Three years of operation. The counter hit 2,147,483,647. The next insert failed with an arithmetic overflow. That table was the system's write path. Everything behind it stalled.

Solution? Migrate the column to BIGINT on 900 million rows. Maintenance window out of schedule. The table should have been BIGINT from day one.

IDENTITY has properties you cannot change after the fact. You cannot reseed it across tables. You cannot pre-allocate ranges for replication nodes. You cannot cycle it back to zero. When you need any of those things, SEQUENCE is the tool.

How each one works

IDENTITY: the column property

A property on a column. The engine generates the value at insert time. Two configuration knobs: seed and step. Nothing else.

CREATE TABLE Customers (
    CustomerID   INT IDENTITY(1, 1) PRIMARY KEY,
    CustomerName NVARCHAR(200) NOT NULL
);

INSERT INTO Customers (CustomerName) VALUES (N'Acme Corp');
-- CustomerID: 1, then 2, then 3...

SEQUENCE: the standalone object

Separate schema object. Create it independently. Call it wherever you need a number.

CREATE SEQUENCE dbo.OrderNumberSequence
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    CACHE 100;

-- Use it in an insert
INSERT INTO Orders (OrderID, CustomerID, TotalAmount)
VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, 1, 150.00);

-- Or grab a number without inserting anything
SELECT NEXT VALUE FOR dbo.OrderNumberSequence;

That last line changes how you architect things. Pre-assign an order number. Pass it to a payment gateway. Insert the row later. Share one counter across three tables. Hand out ranges to replication nodes. None of that works with IDENTITY.

Configuration and performance tradeoffs

IDENTITY gives you seed and increment. SEQUENCE gives you six options. Two of them matter in production: CACHE and CYCLE.

Option	IDENTITY	SEQUENCE
Start value / increment	seed, step	`START WITH`, `INCREMENT BY`
MINVALUE / MAXVALUE	not available	yes
CYCLE (wrap at max)	not available	yes
CACHE (memory range)	not available	yes

CACHE holds a range of numbers in memory. Most allocations skip disk entirely. A bulk insert of 10,000 rows with SEQUENCE CACHE 1000 produces roughly 10 disk writes for number generation. IDENTITY hits the allocation map on every row.

The real bottleneck for concurrent inserts is PAGELATCH_EX contention on the last physical page of the clustered index. Sequential keys. All inserts land on the same data page. That page becomes a hotspot. Threads queue waiting for the latch. CACHE on a SEQUENCE reduces the write load to sys.sequences metadata tables. It does nothing about the physical contention on the target table's last page. Fix that with a non-sequential clustered index strategy, partitioning, or In-Memory OLTP. SEQUENCE with CACHE is not a substitute for any of those.

CYCLE wraps the sequence back to MINVALUE when it hits MAXVALUE. Useful for yearly invoice numbering. Dangerous if older rows remain in the table. The cycled sequence generates numbers that already exist. Every new insert fails with a primary key violation. It keeps failing until you archive or purge the old data. I watched an ordering system go down for a full morning because a CYCLE wrapped without a matching archival job. IDENTITY has no CYCLE option. It just throws an arithmetic overflow.

The replication problem

Merge replication. Two nodes, both inserting rows, both generating IDENTITY values. Without range management, they produce the same numbers. Primary key violations on sync.

SQL Server supports IDENTITY ranges in replication. Node A gets 1 to 1,000,000. Node B gets 1,000,001 to 2,000,000. When a node exhausts its block, it asks the publisher for another. This works. Until it doesn't. Nodes run out of range under load. They fail to negotiate a new block fast enough. Inserts stall while the publisher catches up.

SEQUENCE simplifies this. Each node gets its own sequence with a different START WITH. No range negotiation. No publisher dependency:

-- Node A
CREATE SEQUENCE dbo.DocSeq START WITH 1 INCREMENT BY 1 CACHE 1000;

-- Node B
CREATE SEQUENCE dbo.DocSeq START WITH 1000000000 INCREMENT BY 1 CACHE 1000;

The gap between ranges is deliberate. Node A grows past 100 million? Reseed. Want more headroom? Use BIGINT and start node B at 1,000,000,000,000.

Gaps, by design

Five things cause gaps in both IDENTITY and SEQUENCE: transaction rollback, server restart, cache flush under memory pressure, failed bulk insert, and explicit reseed via DBCC CHECKIDENT. The first four apply to both mechanisms.

BEGIN TRANSACTION;
    INSERT INTO Orders (OrderID, CustomerID, TotalAmount)
    VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, 1, 150.00);
    -- OrderID: 1042
ROLLBACK TRANSACTION;
    -- 1042 is gone. Next insert gets 1043.

The number generator does not participate in your transaction. It hands out a value. Moves its internal pointer forward. Transaction rolls back? Pointer stays. Making it transactional would require a lock that serializes all inserts. Throughput dies.

For surrogate keys, gaps are irrelevant. CustomerID 57 missing does not break any join or any index. Gaps matter when the number has legal or audit significance. Invoice numbers. Tax document IDs. Compliance certificates.

Since SQL Server 2017, you can disable caching for IDENTITY at the database level: ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF. This prevents the jump in IDENTITY values on an unexpected server restart or an Always On failover. The trade-off is a slight insert performance penalty. The engine persists the current identity value more aggressively. Gap size after a failover is a business concern? Turn it off. High-throughput logging table where nobody cares about gaps? Leave the default.

Gapless numbering when you need it

Neither IDENTITY nor SEQUENCE gives you gapless. You need a counter table with explicit locking.

The old way was a "quirky update": SET @NextNumber = LastNumber = LastNumber + 1 inside an UPDATE. It works. It is also an undocumented anti-pattern, and it races on January 1st. Two threads discover the row for the new year does not exist. Both pass the UPDATE with @@ROWCOUNT = 0. Both enter the IF block. Both try to INSERT. First wins. Second gets a primary key violation.

The correct approach is MERGE with HOLDLOCK and the OUTPUT clause:

CREATE TABLE dbo.InvoiceNumberCounter (
    Year INT NOT NULL PRIMARY KEY,
    LastNumber INT NOT NULL
);

BEGIN TRANSACTION;

    DECLARE @AllocatedNumber TABLE (NextNum INT);

    MERGE dbo.InvoiceNumberCounter WITH (HOLDLOCK) AS target
        USING (SELECT YEAR(SYSUTCDATETIME()) AS InvoiceYear) AS source
        ON target.Year = source.InvoiceYear
        WHEN MATCHED THEN
            UPDATE SET LastNumber = LastNumber + 1
        WHEN NOT MATCHED THEN
            INSERT (Year, LastNumber)
            VALUES (source.InvoiceYear, 1)
        OUTPUT inserted.LastNumber INTO @AllocatedNumber;

    DECLARE @NextNumber INT = (SELECT NextNum FROM @AllocatedNumber);

    INSERT INTO Invoices (InvoiceNumber, CustomerID, Amount)
    VALUES (@NextNumber, @CustomerID, @Amount);

COMMIT TRANSACTION;

HOLDLOCK takes a range lock. The row for the current year does not exist? The range lock prevents another session from inserting it. The second MERGE blocks. Then wakes up. Finds the row the first session created. No race condition. No PK violation.

This serializes all invoice creation for a given year into a single queue. For most businesses, fine. Issuing thousands of invoices per second? Talk to your compliance team about whether gapless is truly a legal requirement. The serialization cost is real. There is no way around it.

What I use

Single-table surrogate key. No cross-table needs. No replication. IDENTITY(1, 1). Simplest thing that works.

CREATE TABLE dbo.Products (
    ProductID    INT           IDENTITY(1, 1) NOT NULL,
    ProductName  NVARCHAR(200) NOT NULL,
    CONSTRAINT PK_Products PRIMARY KEY (ProductID)
);

Anything else: SEQUENCE with CACHE 50. Loses at most 50 numbers on a restart. High-throughput workload? CACHE 1000 or higher.

CREATE SEQUENCE dbo.DocumentNumberSeq
    AS INT START WITH 1 INCREMENT BY 1 CACHE 50;

After a data migration, reseed:

DECLARE @MaxID INT = (SELECT ISNULL(MAX(OrderID), 0) FROM Orders);
ALTER SEQUENCE dbo.OrderNumberSequence RESTART WITH @MaxID + 1;

For IDENTITY: DBCC CHECKIDENT ('Orders', RESEED, @MaxID);

One last thing. Use BIGINT for anything you expect to accumulate more than a few hundred million rows. INT buys you 2.1 billion values. Sounds like a lot. Then you have a table that logs every API call, every state change, every event. The counter runs out.

Memory That Forgets: The Missing Primitive in Agent Architecture

Damian Borowski — Wed, 05 Aug 2026 12:21:14 +0000

TL;DR

Agent memory today stores and retrieves. It never takes anything back. When a premise you settled on Monday collapses on Thursday, nothing walks the memory structure and retires the work that stood on it.

We built the missing piece: a persistent hypothesis DAG that propagates failures backwards and put it through a pre-registered adversarial benchmark against two baselines across 30 seeded R&D problems.

The result: 16.8 experiments to goal against 26.1 for a baseline with perfect recall. Thirty seeds, thirty wins, zero losses.

Four things worth knowing before you read on:

The advantage is inferential. The baseline remembered every fact it ever saw and still lost by 9 experiments on the median. The engine retires questions it never has to ask, making deductions that are impossible to represent in a non-graph memory.
It prevents catastrophic lock-in. Left alone, an LLM picks greedily. For an agent running unattended for days, the engine's probabilistic sampling guarantees it never catastrophically locks onto a dead path.
It costs roughly ca. 11x the tokens. The break-even question is blunt: is one of your experiments worth more than 400k tokens? For a $2 API call, no. For a three-day training run, yes, by orders of magnitude.
You can watch it happen. The UI dashboard runs by default and replays any past instant of the search.

Agenda

The honeymoon problem: why agents fall apart on multi-day R&D
Linear thinking vs retrieval-only memory, and why both miss
The question the field has not been asking
What write-back belief revision actually is
Inside a belief DAG: lifecycle, cascading prune, deduction
Watching it change its mind
Choosing what to test next without locking in
What the benchmark showed
What it costs
One Belief State, many projects, models and sessions
Where this goes

1. The Honeymoon Problem

If you have put an autonomous agent on real work, you know the honeymoon.

The first few hours are genuinely impressive. It writes clean code, reads logs, runs commands, moves with intent. Leave the same agent on a multi-day R&D task and something else happens.

By the second day it is looping. It re-runs an experiment it already failed. It has forgotten why it dropped an approach at step 12. And when an assumption it made at step 5 collapses at step 45, it keeps building on everything that assumption produced. Nothing pulls the thread.

This is not a software problem. It is not the context window and it is not model intelligence. It is memory architecture.

2. Linear Thinking vs Retrieval-Only Memory

Essentially, agent memory currently comes in two shapes, and both leak:

Linear thinking: Chain-of-Thought, sequential-thinking tools. The model emits thought steps in order. That works for a single session on a single problem. Real engineering is not a line; it is a branching tree where most branches die. Once the agent walks into a dead end, a linear log gives it nothing to backtrack to.

Retrieval-only memory : vector stores, key-value caches, flat scratchpads. The agent can search what it wrote two days ago. That is passive memory: the store holds text and understands nothing about it. It does not know Configuration B rested on Assumption A, so when A falls, B keeps its green tick.

That is the flaw, and it is fatal: passive memory never revises.

Assumption A leads to Hypothesis B leads to Experiment C. C fails hard. Passive memory writes down "C failed" and stops. It does not mark B suspect. It does not touch the other branches that leaned on A.

We do not need agents that remember more. We need memory that changes its mind and is able to forget.

3. The Question the Field Has Not Been Asking

Before building this we read the field properly: a dozen open-source memory and reasoning projects, source included, not just their READMEs. Production deployments, paid plans, published benchmarks. Serious work by serious people, and several of them are better at what they do than we would be.

The pattern is what matters. Almost all of them are built around one question: "what do I know?" Storage, embedding, retrieval, ranking, write governance - that question is well served and getting better every month. A couple handle contradictions by overwriting the older memory, which is exactly right for the thing they are built for. "I drive a Skoda" becomes "I bought a Tesla" and nothing downstream needs to move.

The question nobody is set up to answer is the other one: "what should I do next, and what did this failure just rule out?" That needs dependency structure, and dependency structure is not something you can bolt onto a retrieval layer afterwards: it has to be the thing you store. So there is a genuine gap here, and it is a gap in emphasis rather than in competence.

We did borrow. For example: Five separate teams had independently built drift detection: flagging knowledge that was true when stored and has since gone stale. Five unconnected teams solving the same problem is a spec, not an opinion, so we built it too.

4. What Write-Back Belief Revision Actually Is

The idea is not new. Assumption-Based Truth Maintenance Systems tracked dependencies between logical statements and retracted every inference derived from a premise the moment that premise fell over.

Put that primitive under an LLM agent and the shape of the memory changes. Instead of a flat file or a pile of independent paragraphs, the agent's working knowledge becomes a directed acyclic graph of hypotheses on top of a SQLite database.

Three mechanisms then run without the model's help:

Write-back propagation. An experiment fails, and the system walks the dependency edges and retracts what rested on it - rather than filing the log and moving on.
Cascading prune. A parent goes INVALIDATED, and every child, grandchild and refinement below it goes PRUNED. No tokens spent reasoning about dead branches.
Exclusion-group inference. The agent is choosing one element out of four. Confirm one and the other three retire themselves as EXHAUSTED. Nothing left to test.
The closed-world assumption is declared, not assumed. Inferences above are sound only if the listed answers are all the answers. For example: which learning rate? always admits another and the engine then withholds both. And when a deduction it did draw turns out to rest on an incomplete list, it is withdrawn rather than defended: the node goes back on the frontier and one probe settles which premise was wrong.

The agent does not implement any of that. It says which hypotheses exist, which ones rest on which, and which ones are competing answers to the same question. The engine does the rest, and keeps doing it every time a result lands.

The load moves off the context window and onto a database that enforces it. The model no longer has to remember that A was disproved.

5. Inside a Belief DAG

Every hypothesis moves through a lifecycle, and each state means something specific:

UNTESTED - on the frontier, eligible for dispatch.
IN_PROGRESS - an agent holds a lease and is running the experiment.
VERIFIED / INVALIDATED - hard evidence came back and settled it.
PRUNED - the node was fine on its own; its parent was not. The subtree collapses without anyone deciding to.
EXHAUSTED - either it was tested and fell short, or a competing alternative in its exclusion group was confirmed and it retired without ever being probed.
NEEDS_REVISION - the integration failed while every component passed alone. The system flags the implicated assumptions instead of blaming all of them.

Look at the two revival edges in that chart. EXHAUSTED → UNTESTED fired 15 times and NEEDS_REVISION → VERIFIED fired 60 times. That is the engine changing its mind - reopening a question it had closed, and clearing an assumption it had put under suspicion. A store that only accumulates has no edges pointing that way.

Inference beats memory

Here is the part that pays for the machinery: deduction by elimination.

An agent is testing five mutually exclusive. It probes candidate 1, 2, 3 and 4. All four fail.

A normal agent now runs the fifth synthesis. A belief DAG does not: with four ruled out and the group asserting exactly one is true, the fifth is entailed. It goes VERIFIED without a probe.

Across 30 benchmark seeds that fired 37 times. Thirty-seven experiments that never had to happen.

metric	value	what it means
Exclusions applied	329	a confirmed value retired its siblings — 329 questions closed for free
Deductions by elimination	37	last one standing, confirmed without a probe
Substitutes ruled out by a sub-par swap	12	a diagnostic rebuild that fell short eliminated the value it swapped in
Duplicate probes (arm B)	0 of 503	nothing was tested twice
Conflicts recorded	15	the integration failed while every part passed alone
Conflicts narrowed to a culprit	15 of 15	every one named the guilty assumption
Destructive revisions	75	belief withdrawn because something built on it failed
Pruned re-executions	0	no settled branch was ever re-run

6. Watching It Change Its Mind

Everything above is a claim about a database. Claims about databases are easy to make and hard to feel, so the belief state ships with a viewer that runs beside the agent by default.

What you are looking at: untested nodes glow at their real chance of being dispatched next, which is not a heuristic — it is how often each one actually wins a Thompson draw. In-progress nodes pulse. The bar along the bottom is the run's own activity over time. Drag it and the graph rewinds, the status history is bi-temporal, so any past instant is a filter clause rather than a snapshot someone remembered to take. The narrative panel rewinds with it, which sounds like a detail until you watch a run and want to know what it believed twenty probes ago.

7. Choosing What to Test Next Without Locking In

Thirty open hypotheses. Which one do you run?

Left alone, an LLM picks greedily, whatever looks best right now. Greedy is fast when it is right. When it hits a decoy it commits everything it has left to a dead path and never comes back. In our ablation, greedy locked onto a bad arm on 7 of 30 seeds, with a worst case of 128 wasted pulls. Thompson Sampling never went past 58.

"Catastrophic lock-in" here means the greedy strategy spent its entire remaining budget on one wrong arm. Thompson Sampling draws probabilistically, so there is always some chance it looks elsewhere: which is exactly why it never gets stuck.

So the server samples. Every hypothesis carries a Beta distribution: fresh nodes start flat, successes push it right, failures push it left. When the agent asks for a target, the navigator draws once from each eligible distribution and takes the highest.

Now the honest part. Thompson Sampling does not beat greedy on the typical seed. Greedy takes the median on 23 of 30, because exploiting hard is a good strategy right up until it is not. What sampling buys is a bounded worst case: 55% below greedy's.

For an agent that might run unattended for days, "never catastrophically locks in" is worth more than "slightly quicker on the easy ones."

strategy	mean regret	median	worst case
Random	116.0	115.5	120.4
Greedy	33.7	5.2	128.6
Thompson Sampling	40.0	38.1	58.0

8. What the Benchmark Showed

We pre-registered an adversarial benchmark and ran three agent architectures across 30 seeded R&D problems. Each problem is a 5-axis by 5-value search space - 3125 combinations, one of which clears the bar. A planted decoy confirms perfectly in isolation and then breaks when you compose it, which is precisely where naive elimination gets the wrong answer.

Three arms:

Arm A - LLM agent with a manual Markdown scratchpad. The ergonomic floor.
Arm F - LLM agent with an auto-persisted, perfect-recall transcript of every probe. The steel-man. It never forgets anything.
Arm B - LLM agent on the full DAG belief state over MCP.

The numbers

criterion	result	threshold	verdict
B vs F — the gate	9.0 steps median, 34.6%, 30/0/0	25%	PASS
B vs A	27.5 steps, 61.8%, 30/0/0	25%	PASS
Navigator	worst case 55% under greedy, 0/30 lock-ins	<= greedy worst case	PASS
Revision	0 pruned re-executions, 105 revision events	0 re-executions	PASS
Status utility	chi-square 37.9, p=5e-5 -> KEEP	measured	PASS

Two things are worth pulling out.

The win is over perfect recall, not over forgetting. Arm F kept every fact it ever recorded. Zero memory loss. It still lost by 9 steps on the median, on every single seed.

The advantage is inferential. Arm F duplicated 1.5% of its probes; arm B duplicated none at all. If the moat were just "remembers better", F would have matched B. It did not, because B retires questions it never has to ask: 329 exclusion inferences and 37 deductions across the run. Neither of those is representable in a flat log.

9. What It Costs

Here is the number that decides whether any of this is for you.

arm	turns per experiment	prompt tokens/turn	tokens per episode	experiments to goal
B - hypotree	1.72	13,306	~392k	16.8
F - auto-transcript	0.32	3,522	~35k	26.1
A - scratchpad	0.32	3,753	~58k	45.0

Arm B spends about 11x the tokens to save 36% of the experiments. In a benchmark where an experiment is a millisecond HTTP call, that trade is terrible — which is the point. We picked the worst possible case deliberately, because a design that only looks good when the measurement flatters it is not a design.

Now invert the arithmetic for real work. If a probe is a three-day training run, a week of lab synthesis, or a fab cycle, then nine fewer experiments is the budget and the token bill is a rounding error. The break-even question is blunt: is one of your experiments worth more than about 400k tokens? For a $2 API call, no. For anything you have to schedule, yes, by orders of magnitude.

11. One Belief State, Many Projects, Models and Sessions

The belief state is a SQLite database, not a context window. That changes who can touch it and when.

Across models. Plan the search with a frontier model, then hand the graph to something cheap that runs probes and records results. The structure carries the logic; the executor only has to follow instructions.

Across sessions. Every VERIFIED hypothesis, every retired alternative, every open conflict is exactly where you left it. Nothing to rebuild.

Across agents. One agent proposes the tree. Another runs experiments. A third reviews conflicts and suggests the discriminating test. Same graph, three readers and writers.

Across projects. The workspace is keyed by project, so your architecture search sit in separate databases and never bleed into each other.

12. Where This Goes

Nothing here is new on its own. Thompson Sampling is from 1933, ATMS from 1986, Beta distributions from Bayes. What is new is wiring them together as a belief layer an agent can write back to.

Those experiments are tracked inside a hypotree DAG. When one fails, the approaches resting on it get pruned by the same machinery they were built to improve:

Recover the half-probe. The v0.4 release traded a little search efficiency for capability, and the free-retirement rate is where it went. The reopen machinery pays for some retirements twice; that is measurable and it is being measured.
Adversarial evidence. We have never tested what the engine does with hostile input- a fabricated result, a forged depth, two agents reporting opposite outcomes on the same node. An engine that can be steered by a lie has a moat made of paper, and we would rather find that out ourselves than have someone find it for us.
Calibration. Everything so far measures efficiency: how few experiments to the goal. Nothing yet measures whether the things it marks VERIFIED are actually true. That is a different question and a harder one, and the closed-world bug is the reason it moved up the list. It would have been embarrassing to publish a precision number measured through a mechanism that was asserting on no evidence.
LLM priors with shrinkage, then PUCT. The agent supplies an estimate and a domain tag; empirical-Bayes shrinkage across tags corrects the model's calibration, turning "the model said 0.9" into "models like this one say 0.9 about things that work 40% of the time". Then replace the sampler's draw with Q + c·P·√N/(1+N) AlphaZero-style's selection rule with the LLM supplying the policy head. The shrinkage is not a nicety: consuming an uncalibrated prior without it is how you bury the right answer under one confident hallucination.

The ecosystem is moving from chatbots that write code to agents that run R&D. For multi-day engineering work: chemistry, materials, biology, ML, automotive, software: flat text memory does not hold. What is needed is a belief layer that revises rather than accumulates, prunes dependents when a premise falls, refuses to lock in on a dead path, and survives every boundary between a model, a session, an agent and a person.

Hypotree is our open-source take on that, shipped as MCP tools for Cursor, Cline, Claude Desktop and whatever loop you have written yourself. It cleared its adversarial gate: 30 seeds, four criteria, all green.

If you are tired of watching your agents re-walk yesterday's dead ends, give them memory that knows how to forget.

Getting Started

One line, no config, no account:

uvx hypotree

That starts the MCP server and the dashboard together. Point your client at it, give an agent a real multi-day problem, and leave the graph open on a second monitor: the first time you watch three hypotheses retire because a fourth was confirmed, the argument in this article stops being an argument.

GitHub: github.com/tygryso/hypotree
PyPI: pip install hypotree