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/FeatureIdentitySequence Object
Allow specifying minimum and/or maximum increment valuesNoYes
Allow resetting the increment valueNoYes
Allow caching increment value generatingNoYes
Allow specifying starting increment valueYesYes
Allow specifying increment valueYesYes
Allow using in multiple tablesNoYes

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:

Last modified: November 27, 2019

Author

Comments

Write a Reply or Comment