SQL Server Error Messages - Msg 11721¶

Error Message¶

Server: 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.

Causes¶

A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence object was created.

The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle or repeat as requested. Sequences, unlike identity columns, are not associated with tables.

A sequence is created independently of table objects by using the CREATE SEQUENCE statement. Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance.

The syntax of the CREATE SEQUENCE statement is as follows:

CREATE SEQUENCE <Sequence Name> AS <integer_type>
 [ START WITH <constant> ]
 [ INCREMENT BY <constant> ]
 [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
 [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
 [ CYCLE | { NO CYCLE } ]
 [ { CACHE [ <constant> ] } | { NO CACHE } ]

To generate a sequence number from a specified sequence object, the NEXT VALUE FOR metadata function is used. Unlike identity column values, which are generated when rows are inserted, an application can obtain the next sequence number before inserting the row by calling the NEXT VALUE FOR function. The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table.

The NEXT VALUE FOR function is a non-deterministic function, and is only allowed in contexts where the number of generated sequence values is well defined. There are a lot of limitations and restrictions with the use of the NEXT VALUE FOR. One of these restrictions is in statements using the DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

Here’s a script that shows the NEXT VALUE FOR used in a UNION ALL statement and therefore generating this error message:

CREATE TABLE [dbo].[JusticeLeague] (
    [Name]          VARCHAR(100),
    [Identity]      VARCHAR(100)
)
GO

CREATE TABLE [dbo].[Avenger] (
    [Name]          VARCHAR(100),
    [Identity]      VARCHAR(100)
)
GO

INSERT INTO [dbo].[JusticeLeague] ( [Name], [Identity] )
VALUES ( 'Batman', 'Bruce Wayne' ),
       ( 'Superman', 'Clark Kent' ),
       ( 'Wonder Woman', 'Princess Diana' )
GO

INSERT INTO [dbo].[Avenger] ( [Name], [Identity] )
VALUES ( 'Captain America', 'Steve Rogers' ),
       ( 'Hulk', 'Bruce Banner' ),
       ( 'Iron Man', 'Tony Stark' )
GO

CREATE SEQUENCE [dbo].[JusticeLeagueSequence] AS INT
START WITH 1000
GO

CREATE SEQUENCE [dbo].[AvengerSequence] AS INT
START WITH 2000
GO

SELECT NEXT VALUE FOR [dbo].[JusticeLeagueSequence] AS [ID],
       [Name], [Identity]
FROM [dbo].[JusticeLeague]
UNION ALL
SELECT NEXT VALUE FOR [db].[AvengerSequence] AS [ID],
       [Name], [Identity]
FROM [dbo].[Avenger]
GO

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

Solution / Work Around¶

To overcome the limitation of the use of NEXT VALUE FOR in statements using UNION or UNION ALL (and even in EXCEPT and INTERSECT) operators, the OPENROWSET rowset function can be used. The OPENROWSET is an alternative to accessing tables in a linked server, or in this case, the local server, and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.

Here’s how the statement will look like that uses the NEXT VALUE FOR statement together with the UNION ALL operator overcoming the limitation with the help of the OPENROWSET rowset function:

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Database=SQLHelper;UID=sqlserverhelper;Pwd=SQL2012', 
                'SELECT NEXT VALUE FOR [dbo].[JusticeLeagueSequence] AS [ID],
                       [Name], [Identity]
                 FROM [dbo].[JusticeLeague]')
UNION ALL
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Database=SQLHelper;UID=sqlserverhelper;Pwd=SQL2012', 
                'SELECT NEXT VALUE FOR [dbo].[AvengerSequence] AS [ID],
                       [Name], [Identity]
                 FROM [dbo].[Avenger]')
GO

Here’s the output of the SELECT statement above:

ID    Name              Identity
----- ----------------- ----------------
1000  Batman            Bruce Wayne
1001  Superman          Clark Kent
1002  Wonder Woman      Princess Diana
2000  Captain America   Steve Rogers
2001  Hulk              Bruce Banner
2002  Iron Man          Tony Stark