What Is a Sequence? How to Use a Sequence in MS SQL Server

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
  • Top articles, research, podcasts, webinars and more delivered to you monthly.

  • © 2021, Experfy Inc. All rights reserved.
    Leave a Comment
    Next Post

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Big Data, Cloud & DevOps
    Cognitive Load Of Being On Call: 6 Tips To Address It

    If you’ve ever been on call, you’ve probably experienced the pain of being woken up at 4 a.m., unactionable alerts, alerts going to the wrong team, and other unfortunate events. But, there’s an aspect of being on call that is less talked about, but even more ubiquitous – the cognitive load. “Cognitive load” has perhaps

    5 MINUTES READ Continue Reading »
    Big Data, Cloud & DevOps
    How To Refine 360 Customer View With Next Generation Data Matching

    Knowing your customer in the digital age Want to know more about your customers? About their demographics, personal choices, and preferable buying journey? Who do you think is the best source for such insights? You’re right. The customer. But, in a fast-paced world, it is almost impossible to extract all relevant information about a customer

    4 MINUTES READ Continue Reading »
    Big Data, Cloud & DevOps
    3 Ways Businesses Can Use Cloud Computing To The Fullest

    Cloud computing is the anytime, anywhere delivery of IT services like compute, storage, networking, and application software over the internet to end-users. The underlying physical resources, as well as processes, are masked to the end-user, who accesses only the files and apps they want. Companies (usually) pay for only the cloud computing services they use,

    7 MINUTES READ Continue Reading »