2 Ways to Apply a Sequence to a Table in SQL Server

In SQL Server, we can create sequence objects in order to generate a range of numbers that increment with each call to the sequence.

Although sequences are similar to identity columns, they are different in that they are generated independently of any table. It’s up to the application to generate the next sequence number before inserting it into the table. Therefore, we can apply a sequence to a table in the same way we would insert any other data into a table.

Here are two ways to apply sequence numbers into a table in SQL Server.

Option 1: Use the `INSERT` Statement

One option is to include the sequence number in the same SQL INSERT statement that we use to insert the data. To do this, we can use the NEXT VALUE FOR function in our INSERT statement.

Example:

CREATE SEQUENCE Sequence1
    START WITH 1
    INCREMENT BY 1;

CREATE TABLE Cats (
    CatId int primary key,
    CatName varchar(60),
    Color varchar(20),
    Meal varchar(20)
);

INSERT INTO Cats( CatId, CatName, Color, Meal ) 
VALUES 
    ( NEXT VALUE FOR Sequence1, 'Meow', 'Yellow', 'Fish' ),
    ( NEXT VALUE FOR Sequence1, 'Fluffy', 'White', 'Beef' ),
    ( NEXT VALUE FOR Sequence1, 'Scratch', 'Yellow', 'Pork' ),
    ( NEXT VALUE FOR Sequence1, 'Purr', 'Brown', 'Fish' ),
    ( NEXT VALUE FOR Sequence1, 'Furball', 'Black', 'Beef' ),
    ( NEXT VALUE FOR Sequence1, 'Scarcat', 'Yellow', 'Beef' ),
    ( NEXT VALUE FOR Sequence1, 'Rolly', 'Black', 'Pork' );

SELECT * FROM Cats;

Result:

CatId  CatName  Color   Meal
-----  -------  ------  ----
1      Meow     Yellow  Fish
2      Fluffy   White   Beef
3      Scratch  Yellow  Pork
4      Purr     Brown   Fish
5      Furball  Black   Beef
6      Scarcat  Yellow  Beef
7      Rolly    Black   Pork

We just created a sequence and a table, inserted data into the table, and then selected its contents.

In this case, we used NEXT VALUE FOR Sequence1 in our INSERT statement to insert the next value from the Sequence1 sequence object into the first column.

The results of the SELECT statement show us that the sequence numbers were inserted as defined.

Option 2: Use a `DEFAULT` Constraint

Another way to do it is to apply the sequence as a DEFAULT constraint against the table definition.

Example:

CREATE SEQUENCE Sequence2
    START WITH 1
    INCREMENT BY 1;

CREATE TABLE Dogs (
    DogId int PRIMARY KEY CLUSTERED DEFAULT (NEXT VALUE FOR Sequence2),
    DogName varchar(60),
    Color varchar(20),
    Meal varchar(20)
);

INSERT INTO Dogs( DogName, Color, Meal ) 
VALUES 
    ( 'Woof', 'Yellow', 'Fish' ),
    ( 'Fluffy', 'White', 'Beef' ),
    ( 'Bite', 'Yellow', 'Pork' ),
    ( 'Bark', 'Brown', 'Fish' ),
    ( 'Fetch', 'Black', 'Beef' );

SELECT * FROM Dogs;

Result:

DogId  DogName  Color   Meal
-----  -------  ------  ----
1      Woof     Yellow  Fish
2      Fluffy   White   Beef
3      Bite     Yellow  Pork
4      Bark     Brown   Fish
5      Fetch    Black   Beef

In this case, instead of including the NEXT VALUE FOR function in our INSERT statement, we included it in the the table definition itself as part of the DEFAULT constraint.

One possible issue with this method is that it’s simply a default value. Therefore, it won’t prevent an INSERT statement from inserting a different value into that column. That said, even the first option doesn’t protect the column from being updated with another value later on. To protect against this, an update trigger could be applied to the table to roll back any changes to the column.

Option 1: Use the INSERT Statement

Option 2: Use a DEFAULT Constraint

Option 1: Use the `INSERT` Statement

Option 2: Use a `DEFAULT` Constraint