IDENTITY and sequences

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Generate surrogate values while recognizing that gaps are possible. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Tickets (TicketID int IDENTITY(1,1) PRIMARY KEY, Label nvarchar(20) NOT NULL);
INSERT INTO #Tickets (Label) VALUES (N'First'), (N'Second');
SELECT TicketID, Label FROM #Tickets ORDER BY TicketID;
DROP TABLE #Tickets;

Read it step by step

1. IDENTITY(1,1) generates values starting at 1 with increment 1 for this table.
2. The INSERT omits TicketID and SQL Server supplies each value.
3. A SEQUENCE is a separate object that can supply numbers to multiple uses; neither mechanism guarantees gap-free business numbering.

Check the result

The displayed 1 and 2 are from this fresh temp table, not a promise for all future inserts.

Practice

Create a disposable SEQUENCE starting at 10 and increasing by 2, show values for Products 1 and 2, then roll back.

BEGIN TRANSACTION;
CREATE SEQUENCE dbo.DDI_LessonSequence AS int START WITH 10 INCREMENT BY 2;
SELECT ProductID,
       NEXT VALUE FOR dbo.DDI_LessonSequence OVER (ORDER BY ProductID) AS TicketID
FROM dbo.Products WHERE ProductID IN (1, 2)
ORDER BY ProductID;
ROLLBACK TRANSACTION;