Limitatoin of Sequence Objects in SQL Server 2012

Sequence Object introduced in SQL Server 2012 is a good features added by MS SQL Server team. I have already discussed this in my post "Sequence Object in SQL Server 2012" but there are many limitations. I will try to explain the prominent limitations.

Let us create a sequence object in our test database.

CREATE SEQUENCE LimitSequence AS INT
 START WITH 1
 INCREMENT BY 1
GO

Also, create a test table with ID generated by using the sequence object;

CREATE TABLE dbo.TestLimit(ID INT,Name VARCHAR(100))
GO
INSERT INTO dbo.TestLimit VALUES
 (NEXT VALUE FOR LimitSequence,'Atif'),
 (NEXT VALUE FOR LimitSequence,'Sheikh'),
 (NEXT VALUE FOR LimitSequence,'Asif')
GO

Cannot Use DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT 

Now, if you try to run the query using sequence object with any of these clauses, you will get an error message;

Select Distinct NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

On executing the above query, you get an eror message as ;

Msg 11721, Level 15, State 1, Line 1
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

This applies to all operators.

Using simple ORDER BY clause

If you try the query using the sequence object with simple ORDER by clause, it will generate an error;

Select NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

Order by ID

Error message is;

Msg 11723, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

Good part for this is to user OVER (ORDER BY ). 

Select NEXT VALUE FOR LimitSequence,*, ROW_NUMBER() Over (Order by ID) as RNO from dbo.TestLimit

The above query will execute without any error.

TOP 

Cannot use with TOP.

Select Top(10) NEXT VALUE FOR LimitSequence,* from dbo.TestLimit

This query will generate error message as;

Msg 11739, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. 

As stated in the error message, if ROWCOUNT is set or TOP and OFFSET is used, the query will generate an error message.

CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF

All these are not allowed. I will give the example if ISNULL function;

Select Isnull( NEXT VALUE FOR LimitSequence,0),* from dbo.TestLimit

Erro rmessage is;

Msg 11741, Level 15, State 1, Line 1

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

You can try the rest of the functions yourself.

WHERE Clause

You cannot use it in WHERE clasuse;

Select * from dbo.TestLimit

where NEXT VALUE FOR LimitSequence = 6

Error message will be;

Msg 11720, Level 15, State 1, Line 2

NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

So, these are few prominent limitation for the sequence objects.