PrismitaLEARN · PRACTICE · GROW

What is the difference between an IDENTITY column and a SEQUENCE?

SQL Server Keys & Constraints Intermediate 2–3 Yrs3–5 Yrs Conceptual 1 min read

Quick Answer

IDENTITY is a column property that auto-generates increasing values on insert, scoped to that one table. SEQUENCE is a standalone schema object that hands out numbers on request (NEXT VALUE FOR), so it can be shared across tables, its value fetched before the insert, and its options (cache, cycle, min/max) tuned.

IDENTITY is a property on a column that auto-increments as you insert, and it belongs to that table. A SEQUENCE is a separate object you ask for the next value from, so you can grab an id before you do the insert, share one sequence across multiple tables, and configure caching. Both can have gaps — a rolled-back insert wastes numbers — so I never rely on the values being consecutive. And to read back the id after an insert I use SCOPE_IDENTITY(), not @@IDENTITY, because @@IDENTITY gets confused by triggers.

  • Both can leave gaps — a rolled-back transaction or a server restart burns numbers. Never assume identity/sequence values are contiguous.
  • Get the last identity: SCOPE_IDENTITY() (safe — current scope) not @@IDENTITY (unsafe — affected by triggers).
  • SEQUENCE advantages: get the id in the app before inserting, use one numbering across several tables, reset without DBCC CHECKIDENT.
  • For distributed systems people often use a GUID (NEWSEQUENTIALID() to keep it index-friendly) instead of either.
All SQL Server questions More on Keys & Constraints