This article basically focuses on the Sequence Limitations and Restrictions with extensive list of example. To know Sequence basics and Sequence Cache Management and internals you may like to visit the articles: Sequence Introduction with Extensive List of examples and Sequence Cache Management and Internals with examples.

In this article let us understand on the limitations and restrictions on the usage of Sequences when using it in queries. Let us understand some of these major limitations with below extensive list of examples.

To demonstrate the limitations let us create a Sequence and a table as below:

--Create A DataBase for Demo 
CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
--Create a Sequence with Initial value 1 and Increment by 1
CREATE SEQUENCE [DBO].[SequenceExample] AS INT
 START WITH 1
 INCREMENT BY 1
GO
--Create Employee Table
CREATE TABLE dbo.Employee(ID INT,Name VARCHAR(100))
GO
-- INSERT RECORDS to the Employee table with Sequence object
INSERT INTO dbo.Employee VALUES
 (NEXT VALUE FOR DBO.SequenceExample,'BASAV'),
 (NEXT VALUE FOR DBO.SequenceExample,'SHREE'),
 (NEXT VALUE FOR DBO.SequenceExample,'PRATHAM')
GO

The NEXT VALUE FOR function cannot be used in the following situations:
1) In a statement using the DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

SELECT (NEXT VALUE FOR DBO.SequenceExample)
UNION
SELECT (NEXT VALUE FOR DBO.SequenceExample)
GO

RESULT:

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.

2) In a statement using the ORDER BY clause unless NEXT VALUE FOR … OVER (ORDER BY …) is used.

SELECT NAME, (NEXT VALUE FOR DBO.SequenceExample) SeqValue
FROM dbo.Employee
ORDER BY NAME
GO

RESULT:

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.

3) In a statement using TOP, OFFSET, or when the ROWCOUNT option is set.

SELECT TOP 10  (NEXT VALUE FOR DBO.SequenceExample)
GO

RESULT:

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.

4) In conditional expressions using CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF.

DECLARE @v INT
SELECT ISNULL(@v,(NEXT VALUE FOR DBO.SequenceExample))
GO

RESULT:

Msg 11741, Level 15, State 1, Line 2
NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

5) In the WHERE clause of a statement.

SELECT *
FROM dbo.Employee
WHERE ID = (NEXT VALUE FOR DBO.SequenceExample)
GO

RESULT:

Msg 11720, Level 15, State 1, Line 3
NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses.

Below table lists few more situations where NEXT VALUE FOR function cannot be used:

Below table lists out the list of error message that Sql Server may return while working with sequences:

We can use query like below to get this list:

SELECT message_id 'ERROR No.',text 'ERROR Description'
FROM SYS.messages
WHERE language_id = 1033 
  AND message_id between 11700 and 11742