CREATE SEQUENCE dbo.tbTable01SequenceGen START WITH 1 INCREMENT BY 1;
SELECT @Table01ID = NEXT VALUE FOR dbo.tbTable01SequenceGen;
You can either use the sequence in the table definition or insert it later when you insert the row.
CREATE TABLE dbo.tbTable01 ( table01_id INT PRIMARY KEY DEFAULT (NEXT VALUE FOR procurement.receipt_no) );
Sequence vs. Identity columns
Sequences, different from the identity columns, are not associated with a table. The relationship between the sequence and the table is controlled by applications. In addition, a sequence can be shared across multiple tables.
The following table illustrates the main differences between sequences and identity columns:
|Allow specifying minimum and/or maximum increment values||No||Yes|
|Allow resetting the increment value||No||Yes|
|Allow caching increment value generating||No||Yes|
|Allow specifying starting increment value||Yes||Yes|
|Allow specifying increment value||Yes||Yes|
|Allow using in multiple tables||No||Yes|
When to use sequences
You use a sequence object instead of an identity column in the following cases:
- The application requires a number before inserting values into the table.
- The application requires sharing a sequence of numbers across multiple tables or multiple columns within the same table.
- The application requires to restart the number when a specified value is reached.
- The application requires multiple numbers to be assigned at the same time. Note that you can call the stored procedure
sp_sequence_get_rangeto retrieve several numbers in a sequence at once.
- The application needs to change the specification of the sequence like maximum value.