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. 

Sequence Object in SQL Server 2012

MS SQL Server has introduced a new feature of Sequence Objects in SQL Server 2012. In the prior versions, IDENTITY was used to generate sequence numbers. The limitation of IDENTITY was that it was related to one table. The scope of Sequence Objects is database. So, if you want to add a same value to two tables, you can use this feature. Also, you can generate the next sequence number before going into the transaction. The IDENTITY is incremented in the transaction at the time of INSERT. Another plus point of Sequence Object is that you can define the maximum value for the object and re-initialize the counter of the object without any extra programming effort.

Here is the code of creating teh new sequence object in SQL Server 2012 Management Studio;

CREATE SEQUENCE dbo.NextCustomerID
    as BIGINT
    START WITH 1
    INCREMENT BY 1;
GO

This will add a new sequence object. You can see this object in Object Explorer under your database in Programming --> Sequence folder.

Well, it not over yet. The query used to create this object creates the object with the default values. You can set many parameters. If you right click the newly created object and click properties, this will open the property window for this object. Here you can set Minimum value, Maximum Value, Cycle and Cache.

Minimum and Maximum are the minimum and maximum values for the object. Cycle check box states that the value will re-initialize when the maximum value is reached. You can also set the re-start value (From where it will start after reaching the maximum value). Cache option tells the object how many values it will have remaining to avoid extra Disk I/Os during the increment process. For example, if cache size is set to 25, it will  not generate 25 numbers in it memory. It will only keep the current value and the remaining values. If 3 IDs are generated for the object with cache of 25, it will have 4 and 22 in its memory. For more information on cache mechanism you can Click Here and check the Cache Management section.

Ok, here is the code for generating the sequence number from the object;

-- Get the next available ID
DECLARE @CustID BIGINT;
SELECT @CustID = NEXT VALUE FOR dbo.NextCustomerID;
SELECT @CustID as 'Customer ID';

Please remember that the sequence object is not transaction specific. Once you generate the sequence with the NEXT VALUE, it is consumed weather the transaction is committed and rolled back. This is similar to the IDENTITY. Hence, the sequence objects also may have gaps in between as we have in IDENTITY columns.

In case you want to restart the sequence, you can use;

ALTER SEQUENCE dbo.NextCustomerID

RESTART

You can use the OVER ORDER BY clause with NEXT VALUE FOR for generating the next sequence values;

SELECT NEXT VALUE FOR dbo.NextCustomerID OVER (ORDER BY RNO) AS ListNumber,

    Name

FROM tblTest ;

You can check books online for more information.

There are also the limitations of the Sequence Objects which I will cover in the separate topic and try to explain with examples.