PrismitaLEARN · PRACTICE · GROW

What is a SEQUENCE, and how is it different from IDENTITY?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

A SEQUENCE is a standalone schema object that hands out numbers via NEXT VALUE FOR dbo.MySeq. Unlike IDENTITY, it's not tied to one column — multiple tables can share it, and you can get the next value before the insert (useful for setting parent and child keys, or building a document number).

A sequence is an independent object that generates numbers — you call NEXT VALUE FOR. The differences from identity are that it isn't attached to a table, so several tables can draw from the same sequence, and you can grab the value before you insert, which is handy when you need the key to set up related rows or to format an invoice number. Both can have gaps from rollbacks, so don't rely on them being consecutive. EF Core uses sequences for its Hi-Lo pattern to allocate id blocks and reduce database round-trips.

  • Define with START WITH, INCREMENT BY, MINVALUE/MAXVALUE, CYCLE, and CACHE n (cache trades a possible gap on restart for speed).
  • Both IDENTITY and SEQUENCE can leave gaps (rollbacks, cache loss) — never assume contiguity.
  • EF Core's Hi-Lo strategy uses a sequence to allocate blocks of ids client-side, cutting round-trips.
All SQL Server questions More on T-SQL