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:
Sl. No. | Limitation/Restriction of using NEXT VALUE FOR function |
1 | In views, in user-defined functions, or in computed columns. |
2 | As an argument to a table-valued function. |
3 | As an argument to an aggregate function. |
4 | In the following clauses: FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY, or FOR XML. |
5 | When a database is in read-only mode. |
6 | In subqueries including common table expressions and derived tables. |
7 | In a MERGE statement. (Except when the NEXT VALUE FOR function is used in a default constraint in the target table and default is used in the CREATE statement of the MERGE statement.) |
8 | In the definition of a check constraint. |
9 | In the definition of a rule or default object. (It can be used in a default constraint.) |
10 | As a default in a user-defined table type. |
11 | In a VALUES clause that is not part of an INSERT statement. |
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
ERROR No. | ERROR Description |
11700 | The increment for sequence object ‘%.*ls’ cannot be zero. |
11701 | The absolute value of the increment for sequence object ‘%.*ls’ must be less than or equal to the difference between the minimum and maximum value of the sequence object. |
11702 | The sequence object ‘%.*ls’ must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, or any user-defined data type that is based on one of the above integer data types. |
11703 | The start value for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object. |
11704 | The current value ‘%.*ls’ for sequence object ‘%.*ls’ must be between the minimum and maximum value of the sequence object. |
11705 | The minimum value for sequence object ‘%.*ls’ must be less than its maximum value. |
11706 | The cache size for sequence object ‘%.*ls’ must be greater than 0. |
11707 | The cache size for sequence object ‘%.*ls’ has been set to NO CACHE. |
11708 | An invalid value was specified for argument ‘%.*ls’ for the given data type. |
11709 | The ‘RESTART WITH’ argument cannot be used in a CREATE SEQUENCE statement. |
11710 | Argument ‘START WITH’ cannot be used in an ALTER SEQUENCE statement. |
11711 | Argument ‘AS’ cannot be used in an ALTER SEQUENCE statement. |
11712 | Argument ‘%.*ls’ can not be specified more than once. |
11714 | Invalid sequence name ‘%.*ls’. |
11715 | No properties specified for ALTER SEQUENCE. |
11716 | NEXT VALUE FOR function does not support the PARTITION BY clause. |
11717 | NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements. |
11718 | NEXT VALUE FOR function does not support an empty OVER clause. |
11719 | NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, or derived tables. |
11720 | NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, or ORDER BY clauses. |
11721 | NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator. |
11722 | NEXT VALUE FOR function is not allowed in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement. |
11723 | NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified. |
11724 | An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function. |
11725 | An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate. |
11726 | Object ‘%.*ls’ is not a sequence object. |
11727 | NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition. |
11728 | The sequence object ‘%.*ls’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated. |
11729 | The sequence object ‘%.*ls’ cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values. |
11730 | Database name cannot be specified for the sequence object in default constraints. |
11731 | A column that uses a sequence object in the default constraint must be present in the target columns list, if the same sequence object appears in a row constructor. |
11732 | The requested range for sequence object ‘%.*ls’ exceeds the maximum or minimum limit. Retry with a smaller range. |
11733 | Parameter ‘%.*ls’ must be a positive integer. |
11734 | NEXT VALUE FOR function is not allowed in the SELECT clause when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. |
11735 | The target table of the INSERT statement cannot have DEFAULT constraints using the NEXT VALUE FOR function when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. |
11736 | Only one instance of NEXT VALUE FOR function per sequence object is allowed in SET or SELECT with variable assignment. |
11737 | NEXT VALUE FOR function does not support the ROWS and RANGE clauses. |
11738 | The use of NEXT VALUE FOR function is not allowed in this context. |
11739 | NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. |
11740 | NEXT VALUE FOR function cannot be used in a default constraint if ROWCOUNT option has been set, or the query contains TOP or OFFSET. |
11741 | NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF. |
11742 | NEXT VALUE FOR function can only be used with MERGE if it is defined within a default constraint on the target table for insert actions. |