Sunday 12 December 2010

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 
image
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:
That is all for now, let me know any remark you may have. Thanks for reading.
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE