New T-SQL Features in SQL Server 2012

SEQUENCE

In SQL Server 2012, Sequence is an object in each database and is similar to IDENTITY in its functionality. 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.

 In SQL Server, Sequence is a much anticipated feature which was available in Oracle for many years. In previous versions of SQL Server, the Identity property is used in a specific table as a Primary key having a non-repeatable value. However, there are several limitations of using the Identity property which can be overcome by the introduction of this new object ‘SEQUENCE’.

Differences between Sequence and Identity

Let’s look at an example of how to create a Sequence object.

USE AdventureWorks;

CREATE SEQUENCE dbo.Seq AS INT

  START WITH 1

  INCREMENT BY 1;

To generate a new sequence of values, you can use NEXT VALUE FOR.

SELECT NEXT VALUE FOR dbo.Seq;

SELECT NEXT VALUE FOR dbo.Seq;

SELECT NEXT VALUE FOR dbo.Seq;

To assign the result into an INSERT statement, let us create two tables:

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 

);

Insert one row into each table. Unlike Identity, Sequence does not guarantee uniqueness. A unique PK constraint must be enforced to the column to guarantee uniqueness.

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 

Notice that the Sequence value is set as 4 and 5, which means that the previous three select statements have incremented the value till 3.

Now, let’s look at an 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;

Here, the list is generated from 6 to 8 and inserted into the table, ordered by Name.

To restart the Sequence, you can 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.

Now, let us take an example of obtaining a long range in one shot.

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

This will increment the Seqeunce object till 4 and the values from 1 to 4 will remain unused anywhere. You can code these unused values as per your new logic anywhere in the table. This cannot be achieved by using the Identity property.

To summarize, the Sequence object has many advantages over the Identity property and its flexibility will definitely help in solving complex T-sql queries.

CONCLUSION

All the new enhancements listed in this article will help developers   writing T-SQL faster with less code and higher performance.