T-SQL Ninja #26

Sequence

Week 26: SEQUENCE

What are SEQUENCEs?

One of the most important SEQUENCEs we already introduced to you in the last Randori. In general, a SEQUENCE object is a generalization of the IDENTITY attribute. While the IDENTITY attribute ensures that every new row is assigned a new number, the SEQUENCE object is an object that creates rows of numbers. Unlike the IDENTITY attribute, it is not bound to another database object (i.e. a table), but is a separate object that can be queried and used.

How can you work with SEQUENCEs?

First you have to define a SEQUENCE object in a new schema. You do this with the CREATE SEQUENCEcommand:

CREATE SCHEMA Test
GO

CREATE SEQUENCE Test.SeqCount 
    START WITH 1
    INCREMENT BY 1;
GO

So now you have created the SEQUENCE object, the parameters were similar to the IDENTITY property the start value (i.e. one) and the increment, i.e. the value by which the value is increased (i.e. one again). To get the next value from the sequence, execute the following statement a few times:

SELECT NEXT VALUE FOR Test.SeqCount

You can see that with every query the value increases by one. Unlike the IDENTITY property of a column, you have other options when creating a sequence. So now let’s create a second sequence:

CREATE SEQUENCE Test.SeqCountMax
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5;
GO

In addition to the start value, you have given this sequence a minimum value (which in this case is equal to the start value) and a maximum value. If you now get the next value of the sequence six times, it will exceed the maximum value:

SELECT NEXT VALUE FOR Test.SeqCountMax

The first five calls will return the values 1 to 5, but the sixth call will return an error message that the sequence has reached its maximum value. What can be done about this? Simple: unlike the Identity property, sequences can be created to “recycle” values:

CREATE SEQUENCE Test.SeqCountCycle
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE;
GO

If you now call the next value six times, you will not get an error on the sixth call, but again the value 1:

SELECT NEXT VALUE FOR Test.SeqCountCycle

But you can not only jump back to the start value, but also to other values, for example we can output negative values in a sequence:

CREATE SEQUENCE Test.SeqCountCycleNeg
    START WITH 1
    INCREMENT BY 1
    MINVALUE -5
    MAXVALUE 5
    CYCLE;
GO

If you now call up the next value of the sequence six times, you will receive the value -5 at the sixth call, which then runs through to +5 and then jumps back to -5:

SELECT NEXT VALUE FOR Test.SeqCountCycleNeg

By the way, your INCREMENT BY can also be negative, in this case the sequence does not count the values up but down, so you can build a kind of “countdown”…

But now for the benefit of sequences. Sequences become really practical when you have to define a kind of “key” over several tables. To demonstrate this, let’s create a sequence and two tables that use this sequence as default value for their Id columns:

DROP SEQUENCE IF EXISTS Test.SeqSharedKey;
CREATE SEQUENCE Test.SeqSharedKey
    START WITH 1
    INCREMENT BY 1;

DROP TABLE IF EXISTS Test.SucceededRuns
DROP TABLE IF EXISTS Test.FailedRuns

CREATE TABLE Test.SuccededRuns 
(
     RunId int NOT NULL DEFAULT (NEXT VALUE FOR Test.SeqSharedKey)
    ,RunName varchar(250) 
)

CREATE TABLE Test.FailedRuns 
(
     RunId int NOT NULL DEFAULT (NEXT VALUE FOR Test.SeqSharedKey)
    ,RunName varchar(250) 
)

Now we insert rows and query the tables:

INSERT INTO Test.SuccededRuns (RunName)
VALUES ('First Succeeded'), ('Second Succeeded')

INSERT INTO Test.FailedRuns (RunName)
VALUES ('First Failed')

INSERT INTO Test.SuccededRuns (RunName)
VALUES ('Third Succeeded')

SELECT 
     RunId
    ,RunName 
FROM Test.SuccededRuns

SELECT 
     RunId
    ,RunName 
FROM Test.FailedRuns

We can see that in Test.SucceededRuns the runs with RunIds 1, 2 and 4 can be found, while the entry with the RunId 3 was entered in Test.FailedRuns.

It is important to note, however, that there is no consistency here. Since the RunId column is not an identity column, we could enter any values here, including duplicates or fantasy values. Unlike a column with an IDENTITY property you would not even notice this, because you are not forced to do an explicit IDENTITY_INSERT, but can simply write into the column and unlike an IDENTITY column the sequence would not “notice” that a higher value is already entered here and continue there but simply continue stubbornly with the next value of the sequence. In such cases, the application would have to guarantee proper use, since the database system cannot necessarily guarantee it.

References

Ninja-Notebooks @ GitHub

Leave a Reply

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