Search results matching tags 'denali', 'T-SQL', 'SQL 11', and 'SQL Server 11'

SQL Server v.Next (Denali) : Deriving sets using SEQUENCE

AaronBertrand — Mon, 13 Dec 2010 12:58:00 GMT

One complaint about SEQUENCE is that there is no simple construct such as NEXT (@n) VALUES FOR so that you could get a range of SEQUENCE values as a set. In a previous post about SEQUENCE, I mentioned that to get a range of rows, you should use the system stored procedure sys.sp_sequence_get_range. If you've tried this stored procedure, you'll know that it has a few issues:

the parameter names are not easy to memorize;
it requires multiple conversions to and from SQL_VARIANT; and,
producing a set from the output requires further processing - all you get is the first and last values.

SEQUENCE has a lot of limitations, and most examples thus far have shown how to use NEXT VALUE FOR to return a scalar value, but one thing you can do is use this mechanism in a limited number of query scenarios. Let's start with a simple SEQUENCE:

USE tempdb;
GO
CREATE SEQUENCE dbo.MySequence
    AS BIGINT
    MINVALUE 1
    NO MAXVALUE
    START WITH 1;

And as a quick query example, there is nothing stopping you from doing this:

WITH fake_and_meaningless_set AS
(
    SELECT x = 'first row'
    UNION ALL
    SELECT x = 'second row'
)
SELECT [Value] = NEXT VALUE FOR dbo.MySequence
FROM fake_and_meaningless_set;

Results:

Value
-----
1
2

Note that in the above case you must use UNION ALL. If you try to use UNION, you will get the following error message, because of when the duplicates would have to be filtered out by the UNION:

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

Anyway, interesting, right? You now see that NEXT VALUE FOR can actually scale across a set. So, given that, it is easy to see how you could construct a generic wrapper that allowed you to generate any number of sequence values as a set instead of just the first and last values. If you need to support a max of 50 values at a time, you could use sys.objects; for 5000, you could use sys.all_columns. For more you could implement various cross joins or you could use any of the number generators I've described before. Here is an example that assumes you won't ever need more than 5,000 sequence values to be assigned at one time:

DECLARE @RangeSize INT = 250;

WITH fake_set AS
(
    SELECT TOP (@RangeSize) column_id
        FROM sys.all_columns
)
SELECT [Value] = NEXT VALUE FOR dbo.MySequence
FROM fake_set;

Results:

Value
-----
3
4
...
251
252

And if you need to support more, you can do this:

DECLARE @RangeSize INT = 50000;

WITH fake_set AS
(
    SELECT TOP (@RangeSize) column_id
        FROM sys.all_columns AS s1
        CROSS JOIN sys.all_columns AS s2
)
SELECT [Value] = NEXT VALUE FOR dbo.MySequence
FROM fake_set;

Results:

Value
-----
253
254
...
50251
50252

This runs a lot faster than you might expect. Also note that not all of the rows will materialize; so, for example, if the cross join above yields 30 million rows, you won't use up that many SEQUENCE values - only the rows that are returned by TOP.

You can place this code in a stored procedure, but I thought it would be more useful outside of that context because, with the T-SQL above, you could join the results with other tables or views. Unfortunately, you can't place this code into an inline table-valued function, since a function cannot have side effects (the side effect would be assigning new sequence values). Here is the error message you will get if you try:

Msg 11719, Level 15, State 1, Procedure , Line
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

So that allows you to generate a range of sequence values based on data you don't have yet (e.g. returning a set of values for an application to use). But what about if you want to return real data with rows that are assigned by the sequence at runtime? This is even easier. Let's say we want to use our sequence to "tag" all objects that have a name that starts with 'sp' - we can simply do this:

SELECT
    [ID] = NEXT VALUE FOR dbo.MySequence,
    name
FROM
    [master].sys.objects
WHERE 
    name LIKE 'sp%';

That's it! Results:

ID    name
----- --------------------------
50253 spt_fallback_db
50254 spt_fallback_dev
50255 spt_fallback_usg
50256 spt_monitor
50257 spt_values
50258 sp_MSrepl_startup
50259 sp_MScleanupmergepublisher

You'll notice these aren't in any particular order; of course not, because we did not specify an ORDER BY clause. As has been discussed on this blog many times before, without ORDER BY, there is no default ordering, and SQL Server is free to return the results in any order it wishes. This also holds true to the order in which it assigns SEQUENCE values. But you will be quickly disappointed if you just add "ORDER BY name" to the above query:

Msg 11723, Level 15, State 1, Line 2
NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.

This gives us a slight hint that NEXT VALUE FOR accepts an OVER clause, so that you can dictate which order the SEQUENCE values are assigned. Sure enough (and I totally missed this when I first looked at the NEXT VALUE FOR topic):

SELECT
    [ID] = NEXT VALUE FOR dbo.MySequence OVER (ORDER BY name),
    name
FROM
    [master].sys.objects
WHERE 
    name LIKE 'sp%';

Results:

ID    name
----- --------------------------
50260 sp_MScleanupmergepublisher
50261 sp_MSrepl_startup
50262 spt_fallback_db
50263 spt_fallback_dev
50264 spt_fallback_usg
50265 spt_monitor
50266 spt_values

While it is true that this returns the results in the desired order even without a final ORDER BY clause, this is just a coincidence and this isn't guaranteed. The OVER (ORDER BY) clause certainly assigned the SEQUENCE values in the order we asked for, but SQL Server is not bound to returning the rows in the exact same order every time. So the proper form of the query should be:

SELECT
    [ID] = NEXT VALUE FOR dbo.MySequence OVER (ORDER BY name),
    name
FROM
    [master].sys.objects
WHERE 
    name LIKE 'sp%'
ORDER BY 
    name;

Now the results are the same as the above, and you know they will be presented the same way when you run the query again tomorrow or next week.

This technique isn't all that different from using ROW_NUMBER() without bothering with a sequence at all, except that the sequence ensures that your values are unique across the domain where you use them. You might also be tempted to just grab the current value from sys.sequences and then add that value to a distinct ROW_NUMBER() from the set. I strongly recommend against that, because it does not block anyone else from using conflicting values. Note that while you can use this technique to guarantee the order of the SEQUENCE values, you still cannot assume that there will be no gaps in the event that another user is generating values at the same time.

Don't forget to clean up:

DROP SEQUENCE dbo.MySequence;

SQL Server v.Next (Denali) : Using SEQUENCE

AaronBertrand — Thu, 11 Nov 2010 18:04:00 GMT

SQL Server 2011 introduces a feature that Oracle professionals will certainly recognize: SEQUENCE. SEQUENCE allows you to define a central place where SQL Server will maintain a counter of values for you. The last-used value is stored in memory and so no storage is required. Here is an example:

CREATE SEQUENCE dbo.OrderIDs
    AS INT
    MINVALUE 1
    NO MAXVALUE
    START WITH 1;

SET NOCOUNT ON;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
2
3

Notice I did not have to create any tables or worry about persisting information to disk in any way. What about reseeding? This is a common problem with IDENTITY columns. You can "reseed" a sequence using ALTER and RESTART WITH:

ALTER SEQUENCE dbo.foo
    RESTART WITH 20;

SET NOCOUNT ON;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
20
21
22

And if you want to roll back the clock or re-use an existing sequence, you can use a lower number with RESTART WITH as well - though I'd be careful with this depending on how you are using the values.

Want to increment by a value other than 1 (the default)? You can use INCREMENT BY:

ALTER SEQUENCE dbo.foo
    RESTART WITH 1
    INCREMENT BY 5;

SET NOCOUNT ON;

SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
6
11

You can start a sequence at a value greater than 1 simply by changing the MINVALUE. You can also start at the low end of the data range, e.g. to use all 4 billion values for INT instead of just the 2 billion positive values. And you can even use INCREMENT BY to count down instead of up; just use a negative value.

You can also ensure that the sequence doesn't use all the possible values, for example if you wanted to have a maximum of 20 values in a TINYINT sequence, you could use the following code to produce a max of 20 values instead of all 255:

CREATE SEQUENCE dbo.foo
    AS TINYINT
    MINVALUE 1
    MAXVALUE 20
    START WITH 1;
GO

When you create such a sequence, you will get the following warning message (note that it is not an error):

The sequence object 'foo' cache size is greater than the number of available values; the cache size has been automatically set to accommodate the remaining sequence values.

And if you actually try to go past the first 20 values:

SELECT NEXT VALUE FOR dbo.foo;
GO 21

You will get this error message:

Msg 11728, Level 16, State 1, Line 2
The sequence object 'foo' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

You can restart the sequence using ALTER SEQUENCE ... RESTART WITH. If you want to use up all of the values and then loop around and start over, you can use the CYCLE option.

Surprisingly, if you simply overflow the datatype, you get the same sequence-related error message as opposed to the more familiar Msg 220 (arithmetic overflow) error:

SELECT NEXT VALUE FOR dbo.foo;
GO 256

It is important to note that SEQUENCE will not provide you a transactionally consistent and gap-free stream of values. For example, just like IDENTITY, if your transaction rolls back, the next value is still taken:

CREATE SEQUENCE dbo.bar
    AS INT
    MINVALUE 1
    NO MAXVALUE 
    START WITH 1;
GO

BEGIN TRANSACTION;
    SELECT NextBar = NEXT VALUE FOR dbo.bar;
ROLLBACK TRANSACTION;
SELECT NextBar = NEXT VALUE FOR dbo.bar;

Results:

NextBar
-----------
1

NextBar
-----------
2

And if you want to check the current value of the sequence without actually using up a value (like checking IDENT_CURRENT() or DBCC CHECKIDENT), you can check the new catalog view, sys.sequences:

SELECT current_value 
    FROM sys.sequences
    WHERE name = 'OrderIDs';

You can also check several other properties of the sequence this way (don't bother trying to use sp_help - all it will tell you is that it is a sequence object).

Want to get a range of SEQUENCE values? Don't use a loop; there's a stored procedure for that: sys.sp_sequence_get_range. Here is a sample:

DECLARE @fv SQL_VARIANT, @lv SQL_VARIANT;

EXEC sys.sp_sequence_get_range
    @sequence_name     = 'OrderIDs',
    @range_size        = 20,
    @range_first_value = @fv OUTPUT,
    @range_last_value  = @lv OUTPUT;

SELECT 
    fv = CONVERT(INT, @fv), 
    lv = CONVERT(INT, @lv),
    next = NEXT VALUE FOR dbo.OrderIDs;

Results:

fv   lv   next
---- ---- ----
 5     24   25

But how does it perform?

I can see significant performance improvements over using a central identity table or a custom increment table (two typical ways this feature has been implemented). In the following example, I show five different ways to manage a central value generator. The first is a custom increment table, a popular method which uses a quirky update and is certainly far from standard. Next there are two methods using an IDENTITY column: one where the old rows are deleted before the next value is generated (for no other reason other than to save space), and the other that doesn't bother to do the delete, letting the table grow as new values are generated. Finally, we use the sequence, once with a procedure wrapper to simplify the changes to existing scripts, and once with the new NEXT VALUE syntax inserted into the code:

USE [master];
GO

-- drop the database if it exists, and the #timing temp table
-- then create the database

IF DB_ID('BookStore') > 0
BEGIN
   DROP DATABASE [BookStore];
END
GO

IF OBJECT_ID('tempdb..#timing') IS NOT NULL
BEGIN
   DROP TABLE #timing;
END
GO

CREATE DATABASE [BookStore];
GO

USE [BookStore];
GO

SET NOCOUNT ON;
GO

-- create a table with an IDENTITY column

CREATE TABLE dbo.CentralIdentity
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY
);

-- create a "roll your own" increment table and seed initial value

CREATE TABLE dbo.CentralIncrement
(
    ID BIGINT PRIMARY KEY
);
GO
INSERT dbo.CentralIncrement(ID) SELECT 0;
GO

-- create a sequence with the same seed and increment properties

CREATE SEQUENCE dbo.CentralSequence
    AS BIGINT
    MINVALUE 1
    NO MAXVALUE
    START WITH 1;
GO

-- stored procedure for increment, which uses quirky update

CREATE PROCEDURE dbo.Central_CreateIncrement
   @NextID BIGINT OUTPUT
AS
BEGIN
   SET NOCOUNT ON;

   UPDATE dbo.CentralIncrement 
       SET @NextID = ID = ID + 1;
END
GO

-- stored procedure for increasing the IDENTITY column
-- with a parameter for deleting the existing value(s) first

CREATE PROCEDURE dbo.Central_CreateIdentity
   @NextID BIGINT OUTPUT,
   @Delete BIT = 0
AS
BEGIN
   SET NOCOUNT ON;

   IF @Delete = 1
   BEGIN
       DELETE dbo.CentralIdentity;
   END

   INSERT dbo.CentralIdentity DEFAULT VALUES;
   SELECT @NextID = SCOPE_IDENTITY();
END
GO

-- stored procedure for handling incrementing the SEQUENCE

CREATE PROCEDURE dbo.Central_CreateSequence
   @NextID BIGINT OUTPUT
AS
BEGIN
   SET NOCOUNT ON;

   SELECT @NextID = NEXT VALUE FOR dbo.CentralSequence;
END
GO

-- simple table that will be the recipient of key values

CREATE TABLE dbo.Orders
(
   OrderID INT PRIMARY KEY
);
GO

-- simple table for tracking timings

CREATE TABLE #timing
(
   Step    VARCHAR(64),
   dt      DATETIME2(7) NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- start each step with a blank orders table
-- this step is handling the quirky increment update

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting increment';
GO

DECLARE
   @NextID BIGINT;

EXEC dbo.Central_CreateIncrement
   @NextID = @NextID OUTPUT;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished increment';
GO

-- this step is handling the identity with the delete

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting identity + delete';
GO

DECLARE
   @NextID BIGINT;

EXEC dbo.Central_CreateIdentity
   @NextID = @NextID OUTPUT,
   @Delete = 1;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished identity + delete';
GO

-- this step is handling the identity without the delete

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting identity without delete';
GO

DECLARE
   @NextID BIGINT;

EXEC dbo.Central_CreateIdentity
   @NextID = @NextID OUTPUT,
   @Delete = 1;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished identity without delete';
GO

-- this step handles the SEQUENCE with the procedure wrapper

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting sequence with PROC';
GO

DECLARE
   @NextID BIGINT;

EXEC dbo.Central_CreateSequence
   @NextID = @NextID OUTPUT;

INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000

INSERT #timing(Step) SELECT 'Finished sequence with PROC';
GO

-- this step handles the SEQUENCE directly

TRUNCATE TABLE dbo.Orders;
GO

INSERT #timing(Step) SELECT 'Starting sequence without PROC';
GO

INSERT dbo.Orders(OrderID) SELECT NEXT VALUE FOR dbo.CentralSequence;
GO 100000

INSERT #timing(Step) SELECT 'Finished sequence without PROC';
GO

-- ok, now we can run a query to measure timings

;WITH t AS 
(
   SELECT
       Step,
       dt,
       rn = ROW_NUMBER() OVER (ORDER BY dt) 
   FROM
       #timing
)
SELECT
   t1.Step,
   dur_s  = DATEDIFF(SECOND, t1.dt, t2.dt)
FROM t AS t1
INNER JOIN t AS t2
ON t1.rn = t2.rn - 1
WHERE t1.rn % 2 = 1
AND t2.rn % 2 = 0;
GO

The results, keeping in mind that this was in a VM on a laptop:

The clear winner is obviously the SEQUENCE operator, since it doesn't have to write the data to disk in order to persist the current value. Next I ran the same tests, but this time turned page compression on for the increment and identity tables, to see if the reduced I/O might let the existing solutions come a little closer. The only change to the above script is in bold:

CREATE TABLE dbo.CentralIdentity
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY
)
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.CentralIncrement
(
    ID BIGINT PRIMARY KEY
)
WITH (DATA_COMPRESSION = PAGE);

The results were indistinguishable. In fact, from run to run, one method would gain a second, and another would lose two; on the next run, the first one would lose three seconds and another would gain one.

I also tried putting a NEXT VALUE FOR command in a user-defined function, to see if there was an easier way to inline the syntax, and see if the UDF overhead would impact the observed benefit. However if you try to create a function with a reference to a sequence object, here is the error you will get (which will also point out a few other places where you shouldn't bother trying to use them - it's a pretty long laundry list):

Msg 11719, Level 15, State 1, Procedure Central_CreateSequence, Line 6
NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

Note also that you can use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT:

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

If you look in sys.messages, you'll see there are several other limitations in the initial implementation of SEQUENCE. Some of these error messages are quite logical, but others are quite interesting. For example, it is okay to reference a sequence object in another database, but not when defined as a default constraint.

Msg 11700 The increment for sequence object '%.*ls' cannot be zero.

Msg 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.

Msg 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.

Msg 11703 The start value for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.

Msg 11704 The current value '%.*ls' for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.

Msg 11705 The minimum value for sequence object '%.*ls' must be less than its maximum value.

Msg 11706 The cache size for sequence object '%.*ls' must be greater than 0.

Msg 11707 The cache size for sequence object '%.*ls' has been set to NO CACHE.

Msg 11709 The 'RESTART WITH' argument cannot be used in a CREATE SEQUENCE statement.

Msg 11710 Argument 'START WITH' cannot be used in an ALTER SEQUENCE statement.

Msg 11711 Argument 'AS' cannot be used in an ALTER SEQUENCE statement.

Msg 11714 Invalid sequence name '%.*ls'.

Msg 11715 No properties specified for ALTER SEQUENCE.

Msg 11716 NEXT VALUE FOR function does not support the PARTITION BY clause.

Msg 11717 NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements.

Msg 11718 NEXT VALUE FOR function does not support an empty OVER clause.

Msg 11719 NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.

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

Msg 11721 NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.

Msg 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.

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

Msg 11724 An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.

Msg 11725 An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate.

Msg 11726 Object '%.*ls' is not a sequence object.

Msg 11727 NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.

Msg 11728 The sequence object '%.*ls' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Msg 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.

Msg 11730 Database name cannot be specified for the sequence object in default constraints.

Msg 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 either a row constructor or under UNION ALL.

Msg 11732 The requested range for sequence object '%.*ls' exceeds the maximum or minimum limit. Retry with a smaller range.

Hope that gives a good taste of SEQUENCE. As you start contemplating the move to the next version of SQL Server, you may want to consider this feature if you are currently using a central identity or increment table - as long as you can get along with its limitations. If you want more official information on SEQUENCE, including a great description on how memory is used to cache sequence values and avoid I/O, you can start at this Books Online topic, Creating and Using Sequence Numbers.

Msg 11700	The increment for sequence object '%.*ls' cannot be zero.
Msg 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.
Msg 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.
Msg 11703	The start value for sequence object '%.*ls' must be between the minimum and maximum value of the sequence object.
Msg 11704	The current value '%.ls' for sequence object '%.ls' must be between the minimum and maximum value of the sequence object.
Msg 11705	The minimum value for sequence object '%.*ls' must be less than its maximum value.
Msg 11706	The cache size for sequence object '%.*ls' must be greater than 0.
Msg 11707	The cache size for sequence object '%.*ls' has been set to NO CACHE.
Msg 11709	The 'RESTART WITH' argument cannot be used in a CREATE SEQUENCE statement.
Msg 11710	Argument 'START WITH' cannot be used in an ALTER SEQUENCE statement.
Msg 11711	Argument 'AS' cannot be used in an ALTER SEQUENCE statement.
Msg 11714	Invalid sequence name '%.*ls'.
Msg 11715	No properties specified for ALTER SEQUENCE.
Msg 11716	NEXT VALUE FOR function does not support the PARTITION BY clause.
Msg 11717	NEXT VALUE FOR function does not support the OVER clause in default constraints, UPDATE statements, or MERGE statements.
Msg 11718	NEXT VALUE FOR function does not support an empty OVER clause.
Msg 11719	NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables.
Msg 11720	NEXT VALUE FOR function is not allowed in the TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, ORDER BY, COMPUTE, or COMPUTE BY clauses.
Msg 11721	NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.
Msg 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.
Msg 11723	NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.
Msg 11724	An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to a table-valued function.
Msg 11725	An expression that contains a NEXT VALUE FOR function cannot be passed as an argument to an aggregate.
Msg 11726	Object '%.*ls' is not a sequence object.
Msg 11727	NEXT VALUE FOR functions for a given sequence object must have exactly the same OVER clause definition.
Msg 11728	The sequence object '%.*ls' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
Msg 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.
Msg 11730	Database name cannot be specified for the sequence object in default constraints.
Msg 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 either a row constructor or under UNION ALL.
Msg 11732	The requested range for sequence object '%.*ls' exceeds the maximum or minimum limit. Retry with a smaller range.