Database Sequence

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:

Property/Feature	Identity	Sequence Object
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_range to retrieve several numbers in a sequence at once.
• The application needs to change the specification of the sequence like maximum value.

Sources:

SQL Server Sequence