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

Identity	Sequence
Table specific	Table-independent
The new value of Identity cannot be obtained before using it in INSERT statement. Therefore it cannot be during UPDATE	The new value can be obtained any time, as well as during UPDATE
It is not possible to add or remove the Identity property from an existing column	It is possible to alter the properties of a Sequence object.
Minimum and maximum values cannot be defined and even cycling is not possible	Minimum and maximum values can be defined and even with cycling
It is not possible to obtain a whole range of new identity values in one shot, letting the application assign the individual values	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

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.

]]>