SQL11 (Denali): New object SEQUENCE

SQL Server 2012 CTP1 is coming up with a new object named SEQUENCE which allows to manage sequence numbers between 2^31 – 1 and 2^31 –1. SEQUENCE solves many problems with respect to using the IDENTITY property because it is not tied to the column and can be used for many columns in different tables. In this capacity, this novelty is useful as alternative solution and in replacement of ROW_NUMBER for some cases. SEQUENCE can be used with UNION ALL but not with Functions, DISTINCT, UNION, EXCEPT and INTERSECT. The way of using is very easy, practical, flexible and  offers better performance than IDENTITY. Creating the right indexes on columns which takes values from SEQUENCE object will help tremendously, indeed.

Creating a SEQUENCE object is simple.

CREATE SEQUENCE dbo.MySeq
    AS INT 
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 100
    CYCLE 
    CACHE 20
;

Now using it:

SELECT NEXT VALUE FOR dbo.MySeq
GO 4

The result would be something like this:

(no column name)

1

2

3

4

If we execute again the previous query, the result will be numbers from 5 to 8, and so on. However, if we want to reinitialise the sequence, it can be done as follows:

ALTER SEQUENCE dbo.MySeq RESTART 

Now using SEQUENCE as an alternative of ROW_NUMBER:

select next value for dbo.MySeq as [nro],  Employees.FirstName   from Employees 

And also in this way:

select next value for dbo.MySeq  over (order by customers.CustomerID ) as [nro], 
       customers.CustomerID,  Customers.ContactName 
 from Customers 

Here are some resources to check out more information about SEQUENCE:

• Creating and Using Sequence Numbers.
  http://msdn.microsoft.com/en-us/library/ff878058(v=SQL.110).aspx
• sp_sequence_get_range (Transact-SQL)
  http://msdn.microsoft.com/en-us/library/ff878352(v=SQL.110).aspx
• SQL Server v.Next (Denali): Using SEQUENCE http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx

That is all for now, let me know any remark you may have. Thanks for reading.