<style> .reveal-up, .reveal-left, .reveal-right, .reveal-stagger > * { opacity: 1 !important; transform: none !important; } </style>
  1. Learn
  2. SQL
  3. Creating and changing data
All lessons in this course

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Lesson 11 of 54 13 minBeginner

IDENTITY, sequences and generated keys

How IDENTITY numbers rows, why gaps are normal, how to get the id you just inserted, when a SEQUENCE is the better tool, and how to copy rows with their original ids.

IDENTITY(seed, increment) makes SQL Server number new rows: the first gets the seed, each later one adds the increment. Plain IDENTITY means IDENTITY(1, 1). The counter belongs to the table, and SQL Server makes sure no two rows ever get the same number, even when many users insert at once.

Interactive

Where IDENTITY gaps come from

Next value1
dbo.Notes
NoteIdBody
(0 rows)
What happened
  1. Run a statement.
Insert a few rows, then try the other statements and watch the counter on the right.

Gaps are normal

A number is taken as soon as an INSERT starts. If the INSERT fails or is rolled back, the number is not given back. Deleted rows leave holes too. Here a failed insert burns number 3 and a delete removes 4:

Query.sql
CREATE TABLE dbo.Notes (    NoteId int          NOT NULL IDENTITY(1, 1) PRIMARY KEY,    Body   nvarchar(20) NOT NULL);INSERT INTO dbo.Notes (Body) VALUES (N'first'), (N'second');INSERT INTO dbo.Notes (Body) VALUES (NULL);   -- fails: Body is NOT NULLGOINSERT INTO dbo.Notes (Body) VALUES (N'third');DELETE FROM dbo.Notes WHERE NoteId = 4;INSERT INTO dbo.Notes (Body) VALUES (N'fourth'); SELECT * FROM dbo.Notes;
Output
Msg 515, Level 16, State 2, Line 6
Cannot insert the value NULL into column 'Body', table 'BuildMart.dbo.Notes'; column does not allow nulls. INSERT fails.

NoteId  Body
------  ------
     1  first
     2  second
     5  fourth

Which id did I just get?

After inserting a row you usually need its new id, for example to insert the order lines of a new order. SCOPE_IDENTITY() returns the last IDENTITY value made by your own statement in your own scope:

Query.sql
INSERT INTO dbo.Orders (CustomerId, OrderDate, Status)VALUES (7, '2024-07-02', 'Pending'); DECLARE @OrderId int = SCOPE_IDENTITY(); INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)VALUES (@OrderId, 1, 1, 18.50), (@OrderId, 3, 1, 9.75); SELECT OrderId, ProductId, Quantity FROM dbo.OrderItems WHERE OrderId = @OrderId;
Output
OrderId  ProductId  Quantity
-------  ---------  --------
     11          1         1
     11          3         1

DECLARE @OrderId int = … creates a variable to hold the id for the next statement; variables get their own lesson in the Programmability module.

FunctionReturnsSafe?
SCOPE_IDENTITY()the last id made in your scope (your batch or procedure)Yes: the normal choice for one row
@@IDENTITYthe last id made on your connection, in any scopeNo: a trigger inserting into another table changes it
IDENT_CURRENT('t')the last id made for table t by anyoneNo: another user’s insert changes it
OUTPUT inserted.Idevery new id, one row eachYes: the choice for many rows

SEQUENCE: a counter of its own

A sequence is a numbering object that is not tied to any table. You ask it for the next value with NEXT VALUE FOR, and you can use it as a column’s DEFAULT, in several tables, or before you insert anything:

Query.sql
CREATE SEQUENCE dbo.DocumentNo AS int START WITH 5000 INCREMENT BY 1;GOCREATE TABLE dbo.Invoices (    InvoiceNo int  NOT NULL CONSTRAINT DF_Invoices_No DEFAULT (NEXT VALUE FOR dbo.DocumentNo) PRIMARY KEY,    OrderId   int  NOT NULL);CREATE TABLE dbo.CreditNotes (    CreditNo  int  NOT NULL CONSTRAINT DF_CreditNotes_No DEFAULT (NEXT VALUE FOR dbo.DocumentNo) PRIMARY KEY,    OrderId   int  NOT NULL); INSERT INTO dbo.Invoices (OrderId) VALUES (1), (2);INSERT INTO dbo.CreditNotes (OrderId) VALUES (6);INSERT INTO dbo.Invoices (OrderId) VALUES (3); SELECT 'Invoice' AS Doc, InvoiceNo AS No, OrderId FROM dbo.InvoicesUNION ALLSELECT 'Credit note', CreditNo, OrderId FROM dbo.CreditNotes;
Output
Doc            No  OrderId
-----------  ----  -------
Invoice      5000        1
Invoice      5001        2
Invoice      5003        3
Credit note  5002        6

Invoices and credit notes share one series of document numbers, so no number is used twice across the two tables. (UNION ALL stacks two results; it is covered in the Joins module.)

Copying rows with their ids

Sometimes you must keep the original numbers, for example when moving data from an old system. SET IDENTITY_INSERT table ON allows explicit values for one table at a time; switch it off straight afterwards:

Query.sql
SET IDENTITY_INSERT dbo.Products ON; INSERT INTO dbo.Products (ProductId, Name, CategoryId, Price, Stock, Unit, AddedOn)VALUES (100, N'Legacy item', 1, 1.00, 0, N'each', '2020-01-01'); SET IDENTITY_INSERT dbo.Products OFF; INSERT INTO dbo.Products (Name, CategoryId, Price, Stock, Unit, AddedOn)VALUES (N'New item', 1, 2.00, 5, N'each', '2024-07-01'); SELECT ProductId, Name FROM dbo.Products WHERE ProductId > 12;
Output
ProductId  Name
---------  -----------
      100  Legacy item
      101  New item

Once a larger value has been inserted, the counter jumps past it: the next automatic id is 101.

Quick check

An INSERT into a table with IDENTITY fails on a CHECK constraint. What happens to the number it was going to use?

Quick check

Which is the safest way to get the ids of 50 rows inserted in one statement?

Practice

Create a sequence dbo.TicketNo that starts at 500 and goes up by 5. Create a table dbo.SupportTickets whose TicketNo defaults to the next sequence value, with a required Subject nvarchar(80). Insert three tickets in one statement and list them.

Show a hint

CREATE SEQUENCE … START WITH 500 INCREMENT BY 5; then a GO before CREATE TABLE. Use DEFAULT (NEXT VALUE FOR dbo.TicketNo).

Show the solution
Solution.sql
CREATE SEQUENCE dbo.TicketNo AS int START WITH 500 INCREMENT BY 5;GOCREATE TABLE dbo.SupportTickets (    TicketNo int          NOT NULL DEFAULT (NEXT VALUE FOR dbo.TicketNo) PRIMARY KEY,    Subject  nvarchar(80) NOT NULL); INSERT INTO dbo.SupportTickets (Subject)VALUES (N'Damaged delivery'), (N'Wrong colour paint'), (N'Invoice query'); SELECT * FROM dbo.SupportTickets;
Output
TicketNo  Subject
--------  ------------------
     500  Damaged delivery
     505  Wrong colour paint
     510  Invoice query

Remember

  • IDENTITY(seed, increment) numbers rows automatically; you cannot write to it unless IDENTITY_INSERT is on.
  • Failed inserts, rollbacks and deletes leave gaps; IDENTITY is unique, not gap-free.
  • Use SCOPE_IDENTITY() for the id of one new row and OUTPUT inserted.Id for many; avoid @@IDENTITY.
  • A SEQUENCE is a counter of its own: NEXT VALUE FOR, usable as a DEFAULT and shared between tables.
  • TRUNCATE resets IDENTITY to the seed; DELETE does not.
0 of 54 lessons completed