Sequences were introduced in SQL Server 2012.
To locate the Sequences in database, log in to SQL Server Management Studio [2012 edition] (SSMS) and navigate to Programmability node under any database. See the below screenshot to locate the Sequence and to get started with SQL Server Sequence:
Before we create our first sequence let us see What is a Sequence.
Sequence is an schema-bound object that generates a sequence of numeric values (auto number) according to the specification with which the sequence was created. You may be wondering that even an Identity property can generate auto-incremented numeric values. Yes, that’s true but there’s a difference between the two. A few of the differences are listed below:
1. Identity property is bound to a table where as Sequence is bound to a schema i.e. a Sequence can be accessed across the tables inside a database (same schema).
2. We can not define the maximum value in case of Identity column where as the same can be defined by setting the “MaxValue” property.
3. The most important difference that can be helpful in many practical scenarios is that we can not get the value of an Identity column before inserting a record where as the next Sequence number for a Sequence Object can be obtained even before inserting a record. We’ll see how this can be done later.
4. There’s a Cache property associated with Sequences where as there’s no such concept with Identity property. Improves performance.
How to Create a Sequence
A Sequence can be created using T-Sql, see the following image. Here I’ve created a Sequence with data type tinyint but you can define a Sequence with any of the Integer data types like smallint, int, bigint etc. If the datatype is not defined the Sequence would default to bigint.
The START value must be between the MINVALUE and MAXVALUE of the sequence object. If you require ascending Sequence use a positive value in INCREMENT otherwise use negative values for descending Sequence object. If not specified INCREMENT defaults to1 and you can specify a 0 (zero) value to it. Cache is used by default to increase performance (optional).
Sequences can also be created using SSMS. See the first image of this tutorial, yes like any other object creation, right click and you have a context menu of New Sequence. Once you click this you have the following dialog box. You can define your Sequence here. I’ve used the same values that were used in the above T-SQL code.
To get a basic of how a Sequence works let us create a table SampleStudent and insert a few records into it as shown below. If you observe carefully StudentId is a primary key, an Identity property. UniversityId is a Sequence which is inserted using Next Value For keyword.
P.S. UniversityId is not an appropriate field name but let us not go into the details of a nomenclature. The idea here is to convey the working of a Sequence.
In the above result set UniversityID for the first student i.e. StudentID = 1 is 2. Is this a bit confusing as the START value was defined as 1. Here’s an important concept about Sequences that needs proper understanding. A sequence can be invoked by any T-Sql statement and it is not essential that it is always used. It’s like a token system in some Banks (to maintain the Queue) where you can get a token number and can walk away without using it. In above example if I execute the following statement just after the bulk INSERT statement in above screenshot; can you guess what would be the output?
SELECT NEXT VALUE FOR dbo.CodingThisSeq
In the above screenshot result set we have a Sequence value till 5 so the above statement will output 6 as the result set. Now let us insert one more student using the following query:
INSERT INTO SampleStudent (UniversityID, FirstName, LarstName)
(NEXT VALUE FOR dbo.CodingThisSeq, ‘Rini’, ‘Jiya’)
What will be the UniversityID for Rini? Yes, it should not be 6 as this Sequence has been already called. Rini will have an UniversityID of 7.
How was it. Please share your views.