In this post, I will explain how Sequence works in SQL Server that has started to be used since the 2012 version. In the Oracle database, sequences have been used for a long time, but in MSSQL Server they have just started to be used with version 2012. The SEQUENCE object is most commonly used to obtain the value for a surrogate key in tables, most commonly used in the same way as IDENTITY is used. To clarify immediately, SEQUENCE is not the same as IDENTITY and is not a replacement for IDENTITY. IDENTITY is the property of a specific column on a concrete table and SEQUENCE is an object. SEQUENCE was added because of some non-IDENTITY features that were still needed.
What are the traits?
First, SEQUENCE is a separate object and is not bound to a specific table and one SEQUENCE object can be used on multiple tables in parallel. Second, SEQUENCE can be altered. Then the SEQUENCE object can define the rank etc…
Just that the same SEQUENCE object can be used on two tables as the primary key was enough for me. Let me remind you that IDENTITY is most commonly used as the primary key in spreadsheets. The problem was that such a primary key could not be used in PARTIALIZED VIEWs that could be greatly improved by performance.
But let’s go in order.
The syntax for SEQUENCE is:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH ]
[ INCREMENT BY ]
[ { MINVALUE [ ] } | { NO MINVALUE } ]
[ { MAXVALUE [ ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]
[ ; ]
Syntax details can be seen on MSDN.
The code for creating one SEQUENCE object is given in Example 1.
USE Test;
CREATE SEQUENCE dbo.seq_My_Test AS INT
START WITH 1
INCREMENT BY 1;
Example 1. Creating a SEQUENCE object
The new SEQUENCE object can also be seen in the Microsoft SQL Server Management Studio.
Created a SEQUENCE object in Microsoft SQL Server Management Studio
If you right-click on the “Sequences” folder in Microsoft SQL Server Management Studio or on an existing SEQUENCE object, you will be presented with a popup menu with options for creating and maintaining SEQUENCE objects.Popup menu for creating and maintaining SEQUENCE objects
In order to get values from the SEQUENCE object, we created in example 1, NEXT VALUE FOR can be used as given in example 2.
SELECT NEXT VALUE FOR dbo.seq_My_Test;
SELECT NEXT VALUE FOR dbo.seq_My_Test;
Example 2. Using a SEQUENCE object to get values
The result of executing the TSQL code from Example 2 The result of invoking the SEQUENCE object from Example 2
Since I already mentioned the use of the same SEQUENCE object on two tables, in example 3 the TSQL code was created to create two tables and insert data into them using the SEQUENCE object.
CREATE TABLE dbo.My_Table1( Seq_ID INT NOT NULL, NameSurname NVARCHAR(50) NOT NULL );
CREATE TABLE dbo.My_Table2 ( Seq_ID INT NOT NULL, NameSurname NVARCHAR(50) NOT NULL );
INSERT INTO dbo.My_Table1(Seq_ID, ImeIPrezime) VALUES(NEXT VALUE FOR dbo.seq_My_Test, N’Ela Ibric’);
INSERT INTO dbo.My_Table2 (Seq_ID, ImeIPrezime) VALUES(NEXT VALUE FOR dbo.seq_My_Test, N’Alen Ibric’);
SELECT * FROM My_Table1
SELECT * FROM My_Table2
Example 3. Using the same SEQUENCE object in two tables
The existing SEQUENCE facility is very easy to restart if the need arises. The TSQL restart of the SEQUENCE object is given in Example 4.
ALTER SEQUENCE seq_My_Test RESTART WITH 1
Example 4. Restarting a SEQUENCE object
A SEQUENCE object can be created with the rank of values that a particular SEQUENCE can generate. The TSQL code for creating a SEQUENCE ranked object is given in Example 5.
CREATE SEQUENCE seqRang_My_Test AS int
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
Example 5. Creating a defined-ranked SEQUENCE object
Using the sp_sequence_get_range function obtains a ranking of values from a specific SEQUENCE object (example 6).
DECLARE @first_value sql_variant ,@last_value sql_variant
EXEC sp_sequence_get_range
@sequence_name = N’seqRang_My_Test’ ,
@range_size = 10 ,
@range_first_value = @first_value OUTPUT ,
SELECT @first_value AS FirstNumber,
@last_value as LastNumber
Example 6. Obtaining a value rank from a SEQUENCE object
When a specific SEQUENCE object is called a sufficient number of times to exceed the maximum value that was defined when creating a SEQUENCE object, then an error will occur as in Figure 3.
Figure 3. An error occurring when a particular SEQUENCE object exceeds a defined maximum value
Finally, the properties of a particular SEQUENCE object can be checked using the system view as given in Example 6.
SELECT * FROM sys.sequences WHERE name = ‘seq_My_Test’;
Example 7. TSQL code to obtain the properties of a particular SEQUENCE object.
The properties of a particular SEQUENCE object can also be accessed through Microsoft SQL Management Studio through the “Properties” option from the popup menu of a specific SEQUENCE object (right-click on a specific SEQUENCE object). Through the form given in Figure 4, the properties of a particular SEQUENCE object can also be changed.
Properties of the SEQUENCE object form
This is all about sequences that are now being used extensively in the MSSQL server. I don’t know if this is something in response to ORACLE by Microsoft that has been using this sequencing technique for a long time
