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.