SQL Server Sequence Object: Explained for Beginners

One of the most useful tools available to us in Microsoft SQL Server is the sequence object. Sequence objects are a great way to let SQL Server track and update an integer value that can be used to populate a primary key column.

In this very brief tutorial, we’ll discuss everything you need to know about SQL Server sequence objects.

We’ll discuss these topics:

1. What are SQL Server sequence objects?
2. The syntax for creating a sequence object.
3. Examples of using a sequence object.
4. Tips and tricks.

Let’s get right into it.

1. What are SQL Server sequence objects?

Sequence objects provide a way to maintain integer values within the SQL Server. They are great to use for primary key columns or any other column where you would like SQL Server to store and maintain integer values, and would like to increase that number periodically. 

As mentioned, sequence objects will store and maintain integer values internally within the SQL Server. This means you don’t need to keep track of integer values yourself.

The most common place you will likely use a sequence object is in primary key columns that are integer data types. Sequence objects can help us keep track of the integer values we are using in a primary key column.

The best way to understand SQL Server sequence objects is to create some and look at a few examples of using them.

2. The syntax for creating a sequence object

The syntax is very easy:

CREATE SEQUENCE <sequence-object-name> 
AS <integer-data-type> 
START WITH <starting-integer-value> 
INCREMENT BY <increment-value>

To demonstrate sequence objects, we’ll create a Books table and then a sequence object to help us maintain values in a primary key column. Here’s the Books table:

CREATE TABLE Books
(
BookID INT PRIMARY KEY,
Title VARCHAR(50),
Author VARCHAR(20),
Price DECIMAL(5, 2)
)

And here’s the syntax to create our sequence object. We’ll call it seq_Books:

CREATE SEQUENCE seq_Books 
AS INT 
START WITH 1
INCREMENT BY 1

Pretty easy so far, but I realize we haven’t really talked about what the heck this does. We’ll pull it all together in the next section.

3. Examples of using a sequence object

Ok, let’s bring it all together and show you how a sequence object is used and how they can help us.

Let’s say way want to insert a row into the Books table. We can basically query the sequence object to tell us the current integer value that is being maintained in the sequence object.

The way we “query the sequence object” is by running the NEXT VALUE FOR statement. This will return the next integer value from our sequence object. We’ll run this statement and assign the returning value to an integer variable:

DECLARE @seqValue INT
SET @seqValue = NEXT VALUE FOR seq_Books

Then, we can use that variable in our INSERT statement:

DECLARE @seqValue INT
SET @seqValue = NEXT VALUE FOR seq_Books

INSERT INTO Books (BookID, Title, Author, Price) 
VALUES (@seqValue, 'As a man thinketh', 'Allen', 14.95)

If we check the content of the table after running this code, we see our BookID value contains the starting value of the sequence object (because when we defined the sequence object, we said START WITH 1):

If we repeat this entire process, but specify data for a different book, we see that the BookID value is properly incremented:

This new value is incremented from the last value by 1 because that’s what we specified in the definition of the sequence object (where we said INCREMENT BY 1).

The sys.sequences system table

One system table that can help us understand what’s happening behind the scenes is the sys.sequences table. This table outlines all the details we need to know about our sequence objects.

For example, let’s take a look at a few details for our seq_Books sequence object:

Here, we see what we set as our start value and the increment value when we created the sequence object. But the most useful data we see here is current_value. This represents the last integer value that was generated from the sequence object.

So if we do another NEXT VALUE FOR call to the sequence object,  we should see this number increase by one:

Notice this time we didn’t even use the returned integer value for anything. Couldn’t matter less. When we query the sequence object by running NEXT VALUE FOR, the internal integer value will be incremented automatically. This incremented value will be set as the new current value.

To drive the point home, I’ll run the same exact code again:

Again, current_value is updated!

Folks, this is why we say the integer value is maintained by the SQL Server. You yourself don’t need to keep track of the last integer value generated. You just call your sequence object using NEXT VALUE FOR and SQL Server will give you the next integer value, then handle the task of making sure your current_value is updated in the sequence object.

4. Tips and tricks.

Here is a list of a few tips and tricks you should know about SQL Server sequence objects:

Tip # 1: A sequence object isn’t linked to any particular table

You might have noticed that our sequence object (called seq_Books) isn’t actually linked to the Books table in any way.

Folks, a sequence object simply maintains an integer value, and returns the next integer value when running NEXT VALUE FOR.  That is all. A sequence object is not tied to a specific column in a table.

For example, I’ll create a dummy table called Spaghetti and give it a single integer column:

Then, I’ll call NEXT VALUE FOR for our seq_Books sequence object and use the return value to populate the integer column in our Spaghetti table:

So again, there is nothing saying that a sequence object is restricted to use in just one column. If you want a sequence object to be used only for one specific column, the best thing you can do is name the sequence object appropriately. For example, we named our object “seq_Books”, which ought to be a clue that this sequence object should be used for the Books table.

Tip # 2: You can update the current_value of a sequence object by running an ALTER SEQUENCE statement

Let’s take a look at the state of the current_value of our seq_Books object:

Again, this number represents the last sequence value that was generated from the sequence object. If we want to basically reset the sequence object and give it a starting value of 4, for example, we can run the following code:

All we have to do is run a RESTART WITH statement within an ALTER SEQUENCE statement. This will reset the current_value to the integer we specify! Understand that the next time we call NEXT VALUE FOR, this value will be returned and not the next value after that in the sequence.

Tip # 3: Sequence objects don’t enforce uniqueness

I usually see sequence objects used to help populate primary key columns. Primary key values need to be 100% unique across all the rows in a table. When you use a sequence object to help populate a primary key column, understand that it is the primary key constraint that is enforcing uniqueness and not the sequence object.

For example, we’ll create another table called CollegeMajors with an ID column that does not use a primary key constraint:

CREATE TABLE CollegeMajors
(
MajorID INT,
MajorName VARCHAR(30)
)

Then we’ll create a sequence object to help us populate the ID column. This time we’ll use the TINYINT data type and different starting and incrementing values:

CREATE SEQUENCE seq_CollegeMajors
AS TINYINT
START WITH 10
INCREMENT BY 5

Then we’ll use the sequence object to help insert two rows:

Now, let’s reset the current_value of the sequence object, then insert another row:

Notice this new row contain the same MajorID value as an existing row. Again folks, the sequence object does not care about uniqueness. It just generates the next integer in the sequence. If you want your values to be unique, you need to set up a primary key constraint on that column or maybe even a unique constraint.

Tip # 4: The default INCREMENT BY value is 1

I’ll remind you of the syntax for creating a sequence object:

CREATE SEQUENCE <sequence-object-name> 
AS <integer-data-type> 
START WITH <starting-integer-value> 
INCREMENT BY <increment-value>

If you want, you can just leave off the INCREMENT BY value altogether, like this:

CREATE SEQUENCE <sequence-object-name>
AS <integer-data-type>
START WITH <starting-integer-value>

In this case, the INCREMENT BY value defaults to 1.

While we’re at it, you could also leave off the START WITH value and the starting value will default to the smallest value allowed by the data type. For example, the smallest value allowed for the TINYINT data type is 0, so if you created a sequence object that uses TINYINT and left off the START WITH value, the sequence object would start with 0.

Tip # 5: Drop a sequence objects using the DROP SEQUENCE statement

We can drop our sequence object using the DROP SEQUENCE statement. Here’s an example:

DROP SEQUENCE seq_Books

Next Steps:

Leave a comment if you found this tutorial helpful!

Do you know how the IDENTITY property works?

The IDENTITY property is another great tool we can use to populate an integer column. It is also commonly used on primary key columns. You should definitely check out the full beginner-friendly tutorial:

IDENTITY Column in SQL Server: Everything you need to know

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!