SEQUENCE: T-SQL Features in SQL Server 2012

SEQUENCE: T-SQL Features in SQL Server 2012

In my previous posts we have seen what is Transact-Sql in sql server and What are new top features of T-Sql in server. This is one of the best sql server interview Questions and answers. 

·        In SQL Server 2012, Sequence is an object in each database and is similar to IDENTITY in its functionality.

·        In previous versions of SQL Server, the Identity property is used in a specific table as a Primary key having a non-repeatable value.

·        Sequence is an object that has start value, increment value and an end value defined in it.

·        It can be added to a column whenever required rather than defining an identity column individually for tables.

·        It is not possible to add or remove the IDENTITY property from an existing column where as it is possible to alter the properties of a Sequence object.

·        In SEQUENCE, minimum and maximum values can be defined and even with cycling is possible but in IDENTITY, minimum and maximum values cannot be defined and cycling is not possible.

·        It is possible to obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range, letting the application assign the individual values for increased performance but it is not possible in IDENTITY.

Examples:
Creating Sequence Object:

USE AdventureWorks;

CREATE SEQUENCE dbo.Seq AS INT

  START WITH 1

  INCREMENT BY 1;

Generating a new sequence of values:

SELECT NEXT VALUE FOR dbo.Seq;

SELECT NEXT VALUE FOR dbo.Seq;

SELECT NEXT VALUE FOR dbo.Seq;

Result:

           
Creating two tables to assign the result into an INSERT statement:

CREATE TABLE dbo.Examp1

(

  Seq INT NOT NULL,

  Name VARCHAR (50) NOT NULL

);

CREATE TABLE dbo.Examp2

(

  Seq INT NOT NULL,

  Name VARCHAR (50) NOT NULL 

);

Inserting one row into each table:

INSERT INTO dbo.Examp1 (Seq, Name) VALUES (NEXT VALUE FOR dbo.Seq, ‘Tom’);

INSERT INTO dbo.Examp2 (Seq, Name) VALUES (NEXT VALUE FOR dbo.Seq, ‘Jerry’);

SELECT * FROM Examp1

SELECT * FROM Examp2 

Result:

Here, Sequence value is set as 4 and 5, which means that the previous three select statements have incremented the value till 3.
Example of using Sequence with OVER ordering on any item:

INSERT INTO dbo.Examp1 (Seq, Name)

  SELECT NEXT VALUE FOR dbo.Seq

OVER (ORDER BY name ASC), ‘List’

  FROM (SELECT name

        FROM sys.objects

        ORDER BY object_id DESC

        OFFSET 10 ROWS FETCH FIRST 3 ROWS ONLY) Temp;

Result:

Here, the list is generated from 6 to 8 and inserted into the table, ordered by Name.
Syntax for Restarting Sequence:

·        To restart the Sequence, alter the object to start with the required value.

ALTER SEQUENCE seq

RESTART WITH 1

This will restart Seq with 1 and follow the same increment as defined earlier.

Example of obtaining a long range in one shot using Sequence:

·        It is possible to obtain a whole range of new sequence values in one shot using the stored procedure “sp_sequence_get_range”, letting the application assign the individual values for increased performance.

CREATE SEQUENCE SeqRange

    AS INT

    START WITH 1

    INCREMENT BY 1

    MINVALUE 1

    MAXVALUE 25   

DECLARE @first_value sql_variant,

                   @last_value sql_variant

EXEC sp_sequence_get_range

@sequence_name = N’SeqRange’,

@range_size = 4,

@range_first_value = @first_value OUTPUT,

@range_last_value = @last_value OUTPUT;

SELECT @first_value AS FirstNumber, @last_value as LastNumber

Result:

·        This will increment the Sequence object till 4 and the values from 1 to 4 will remain unused anywhere.

·        Unused values can be coded as per new logic anywhere in the table. This cannot be achieved by using the Identity property.

 

If you have any queries or suggestions, please feel free to ask in comments section.