NEXT VALUE FOR (Transact-SQL)

The NEXT VALUE FOR function cannot be used in the following situations:

• When a database is in read-only mode.

• As an argument to a table-valued function.

• As an argument to an aggregate function.

• In subqueries including common table expressions and derived tables.

• In views, in user-defined functions, or in computed columns.

• In a statement using the DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.

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

• In the TOP, OFFSET, FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, WHERE, GROUP BY, HAVING, ORDER BY, COMPUTE, COMPUTE BY, FOR XML clauses.

• In a VALUES clause that is not part of an INSERT statement.

• In the definition of a check constraint.

• In the definition of a rule or default object. (It can be used in a default constraint.)

• As a default in a user-defined table type.

When using the NEXT VALUE FOR function in a default constraint, the following rules apply:

• A single sequence object may be referenced from default constraints in multiple tables.

• The table and the sequence object must reside in the same database.

• The user adding the default constraint must have REFERENCES permission on the sequence object.

• A sequence object that is referenced from a default constraint cannot be dropped before the default constraint is dropped.

• The same sequence number is returned for all columns in a row if multiple default constraints use the same sequence object, or if the same sequence object is used both in the statement supplying the values, and in a default constraint being executed.

• References to the NEXT VALUE FOR function in a default constraint cannot specify the OVER clause.

• A sequence object that is referenced in a default constraint can be altered.

• In the case of an INSERT … SELECT or INSERT … EXEC statement where the data being inserted comes from a query using an ORDER BY clause, the values being returned by the NEXT VALUE FOR function will be generated in the order specified by the ORDER BY clause.

The NEXT VALUE FOR function supports generating sorted sequence values by applying the OVER clause to the NEXT VALUE FOR call. By using the OVER clause, a user is guaranteed that the values being returned are generated in the order of the OVER clause's ORDER BY subclause. The following additional rules apply when using the NEXT VALUE FOR function with the OVER clause:

• Multiple calls to the NEXT VALUE FOR function for the same sequence generator in a single statement must all use the same OVER clause definition.

• Multiple calls to the NEXT VALUE FOR function that reference different sequence generators in a single statement can have different OVER clause definitions.

• An OVER clause applied to the NEXT VALUE FOR function does not support the PARTITION BY sub clause.

• If all calls to the NEXT VALUE FOR function in a SELECT statement specifies the OVER clause, an ORDER BY clause may be used in the SELECT statement.

• The OVER clause is allowed with the NEXT VALUE FOR function when used in a SELECT statement or INSERT … SELECT … statement. The NVF function is not allowed in UPDATE or MERGE statements.

• If another process is accessing the sequence object at the same time, the numbers returned could have gaps.

For information about sequences, query the sys.sequences catalog view.

Requires UPDATE permission on the sequence object or the schema of the sequence. For an example of granting permission, see example F later in this topic.

Sequence objects support ownership chaining. If the sequence object has the same owner as the calling stored procedure, trigger, or table (having a sequence object as a default constraint), no permission check is required on the sequence object. If the sequence object is not owned by the same user as the calling stored procedure, trigger, or table, a permission check is required on the sequence object.

When the NEXT VALUE FOR function is used as a default value in a table, users require both INSERT permission on the table, and UPDATE permission on the sequence object, to insert data using the default.

• If the default constraint has the same owner as the sequence object, no permissions are required on the sequence object when the default constraint is called.

• If the default constraint and the sequence object are not owned by the same user, permissions are required on the sequence object even if it is called through the default constraint.

To audit the NEXT VALUE FOR function, monitor the SCHEMA_OBJECT_ACCESS_GROUP.

The following example creates a sequence named CountBy1 that increases by one every time that it is used.

SELECT NEXT VALUE FOR Test.CountBy1 AS FirstUse;
SELECT NEXT VALUE FOR Test.CountBy1 AS SecondUse;

Here is the result set.

FirstUse

1

SecondUse

2

The following example demonstrates three ways to set a variable to the next value of a sequence number.

DECLARE @myvar1 int = NEXT VALUE FOR Test.CountBy1
DECLARE @myvar2 int ;
DECLARE @myvar3 int ;
SET @myvar2 = NEXT VALUE FOR Test.CountBy1 ;
SELECT @myvar3 = NEXT VALUE FOR Test.CountBy1 ;
SELECT @myvar1 AS myvar1, @myvar2 AS myvar2, @myvar3 AS myvar3 ;
GO

Using the NEXT VALUE FOR function in the definition of a default constraint is supported. For an example of using NEXT VALUE FOR in a CREATE TABLE statement, see Example CSequence Numbers. The following example uses ALTER TABLE to add a sequence as a default to a current table.

CREATE TABLE Test.MyTable
(
    IDColumn nvarchar(25) PRIMARY KEY,
    name varchar(25) NOT NULL
) ;
GO

CREATE SEQUENCE Test.CounterSeq
    AS int
    START WITH 1
    INCREMENT BY 1 ;
GO

ALTER TABLE Test.MyTable
    ADD 
        DEFAULT N'AdvWorks_' + 
        CAST(NEXT VALUE FOR Test.CounterSeq AS NVARCHAR(20)) 
        FOR IDColumn;
GO

INSERT Test.MyTable (name)
VALUES ('Larry') ;
GO

SELECT * FROM Test.MyTable;
GO

The following example creates a table named TestTable and then uses the NEXT VALUE FOR function to insert a row.

CREATE TABLE Test.TestTable
     (CounterColumn int PRIMARY KEY,
    Name nvarchar(25) NOT NULL) ; 
GO

INSERT Test.TestTable (CounterColumn,Name)
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ;
GO

SELECT * FROM Test.TestTable; 
GO

The following example uses the SELECT … INTO statement to create a table named Production.NewLocation and uses the NEXT VALUE FOR function to number each row.

USE AdventureWorks2008R2 ; 
GO

SELECT NEXT VALUE FOR Test.CountBy1 AS LocNumber, Name 
    INTO Production.NewLocation
    FROM Production.Location ;
GO

SELECT * FROM Production.NewLocation ;
GO

The following example grants UPDATE permission to a user named AdventureWorks\Larry permission to execute NEXT VALUE FOR using the Test.CounterSeq sequence.

GRANT UPDATE ON OBJECT::Test.CounterSeq TO [AdventureWorks\Larry] ;