NEXT VALUE FOR (Transact-SQL)NEXT VALUE FOR (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

通过指定的序列对象生成序列号。Generates a sequence number from the specified sequence object.

有关创建和使用序列的完整讨论,请参阅序列号For a complete discussion of both creating and using sequences, see Sequence Numbers. 可以使用 sp_sequence_get_range 保留一定范围内的序列号。Use sp_sequence_get_range to generate reserve a range of sequence numbers.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
NEXT VALUE FOR [ database_name . ] [ schema_name . ]  sequence_name  
   [ OVER (<over_order_by_clause>) ]  

参数Arguments

database_namedatabase_name
包含序列对象的数据库的名称。The name of the database that contains the sequence object.

schema_nameschema_name
包含序列对象的架构的名称。The name of the schema that contains the sequence object.

sequence_name sequence_name
生成该编号的序列对象的名称。The name of the sequence object that generates the number.

over_order_by_clauseover_order_by_clause
确定将序列值分配给分区中的行的顺序。Determines the order in which the sequence value is assigned to the rows in a partition. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

返回类型Return Types

使用序列类型返回一个数字。Returns a number using the type of the sequence.

RemarksRemarks

可以在存储过程和触发器中使用 NEXT VALUE FOR 函数。The NEXT VALUE FOR function can be used in stored procedures and triggers.

在查询或默认约束中使用 NEXT VALUE FOR 函数时,如果多次使用相同的序列对象,或者在提供这些值的语句以及执行的默认约束中使用相同的序列对象,则为结果集的行中引用同一序列的所有列返回相同的值。When the NEXT VALUE FOR function is used in a query or default constraint, if the same sequence object is used more than once, or if the same sequence object is used both in the statement supplying the values, and in a default constraint being executed, the same value will be returned for all columns referencing the same sequence within a row in the result set.

NEXT VALUE FOR 函数具有不确定性,只允许在明确定义了生成的序列值数目的上下文中使用。The NEXT VALUE FOR function is nondeterministic, and is only allowed in contexts where the number of generated sequence values is well defined. 下面定义了给定语句中的每个引用的序列对象使用的值数目:Below is the definition of how many values will be used for each referenced sequence object in a given statement:

  • SELECT - 对于每个引用的序列对象,将为语句结果中的每一行生成一次新值。SELECT - For each referenced sequence object, a new value is generated once per row in the result of the statement.

  • INSERT ... VALUES - 对于每个引用的序列对象,将为语句中的每一个插入行生成一个新值。INSERT ... VALUES - For each referenced sequence object, a new value is generated once for each inserted row in the statement.

  • UPDATE - 对于每个引用的序列对象,将为语句所更新的每一行生成一个新值。UPDATE - For each referenced sequence object, a new value is generated for each row being updated by the statement.

  • 过程语句(如 DECLARESET 等)- 对于每个引用的序列对象,将为每个语句生成一个新值。Procedural statements (such as DECLARE, SET, etc.) - For each referenced sequence object, a new value is generated for each statement.

限制和局限Limitations and Restrictions

不能在下列情况下使用 NEXT VALUE FOR 函数: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.

  • 在使用 DISTINCT UNION UNION ALL EXCEPTINTERSECT 运算符的语句中。In a statement using the DISTINCT, UNION, UNION ALL, EXCEPT or INTERSECT operator.

  • 在使用 ORDER BY 子句的语句中,除非使用了 NEXT VALUE FOR … OVER (ORDER BY …) 。In a statement using the ORDER BY clause unless NEXT VALUE FOR ... OVER (ORDER BY ...) is used.

  • 在以下子句中:FETCH、OVER、OUTPUT、ON、PIVOT、UNPIVOT、GROUP BY、HAVING、COMPUTE、COMPUTE BY 或 FOR XML 。In the following clauses: FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY, or FOR XML.

  • 在使用 CASE CHOOSE COALESCE IIF ISNULLNULLIF 的条件表达式中。In conditional expressions using CASE, CHOOSE, COALESCE, IIF, ISNULL, or NULLIF.

  • 在不属于 INSERT 语句的 VALUES 子句中。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.

  • 在使用 TOPOFFSET 的语句中,或在设置 ROWCOUNT 选项时。In a statement using TOP, OFFSET, or when the ROWCOUNT option is set.

  • 在语句的 WHERE 子句中。In the WHERE clause of a statement.

  • MERGE 语句中。In a MERGE statement. (在目标表的默认约束中使用 NEXT VALUE FOR 函数并且在 MERGE 语句的 CREATE 语句中使用默认值的情况下例外。)(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.)

在默认约束中使用序列对象Using a Sequence Object in a Default Constraint

在默认约束中使用 NEXT VALUE FOR 函数时,下列规则适用: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.

  • 添加默认约束的用户必须对序列对象具有 REFERENCES 权限。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.

  • 默认约束中对 NEXT VALUE FOR 函数的引用不能指定 OVER 子句。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.

  • 如果 INSERT ... SELECTINSERT ... EXEC 语句中插入的数据来自使用 ORDER BY 子句的查询,则按照 ORDER BY 子句指定的顺序生成 NEXT VALUE FOR 函数返回的值。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.

通过 OVER ORDER BY 子句使用序列对象Using a Sequence Object with an OVER ORDER BY Clause

通过将 OVER 子句应用于 NEXT VALUE FOR 调用,NEXT VALUE FOR 函数支持生成排序的序列值。The NEXT VALUE FOR function supports generating sorted sequence values by applying the OVER clause to the NEXT VALUE FOR call. 通过使用 OVER 子句,可以向用户保证返回的值是按照 OVER 子句的 ORDER BY 子句的顺序生成的。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. NEXT VALUE FOR 函数与 OVER 子句一起使用时,下列附加规则适用:The following additional rules apply when using the NEXT VALUE FOR function with the OVER clause:

  • 如果在单个语句中为相同序列生成器多次调用 NEXT VALUE FOR 函数,这些调用必须使用相同的 OVER 子句定义。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.

  • 如果在单个语句中多次调用 NEXT VALUE FOR 函数以引用不同的序列生成器,则这些调用可以具有不同的 OVER 子句定义。Multiple calls to the NEXT VALUE FOR function that reference different sequence generators in a single statement can have different OVER clause definitions.

  • NEXT VALUE FOR 函数应用的 OVER 子句不支持 PARTITION BY 子子句。An OVER clause applied to the NEXT VALUE FOR function does not support the PARTITION BY sub clause.

  • 如果 SELECT 语句中对 NEXT VALUE FOR 函数的所有调用均指定 OVER 子句,则可以在 SELECT 语句中使用 ORDER BY 子句。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.

  • SELECT 语句或 INSERT ... SELECT ... 语句中使用时,允许将 OVER 子句与 NEXT VALUE FOR 函数一起使用。The OVER clause is allowed with the NEXT VALUE FOR function when used in a SELECT statement or INSERT ... SELECT ... statement. 不允许在 UPDATEMERGE 语句中将 OVER 子句与 NEXT VALUE FOR 函数一起使用。Use of the OVER clause with the NEXT VALUE FOR 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.

元数据Metadata

有关序列的信息,请查询 sys.sequences 目录视图。For information about sequences, query the sys.sequences catalog view.

SecuritySecurity

权限Permissions

要求对序列对象或序列的架构具有 UPDATE 权限。Requires UPDATE permission on the sequence object or the schema of the sequence. 有关授予权限的示例,请参阅本主题后面的示例 F。For an example of granting permission, see example F later in this topic.

所有权链接Ownership Chaining

序列对象支持所有权链接。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.

如果在表中将 NEXT VALUE FOR 函数作为默认值,则用户需要对表具有 INSERT 权限和对序列对象具有 UPDATE 权限,才能使用默认值插入数据。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.

审核Audit

若要审核 NEXT VALUE FOR 函数,请监视 SCHEMA_OBJECT_ACCESS_GROUP。To audit the NEXT VALUE FOR function, monitor the SCHEMA_OBJECT_ACCESS_GROUP.

示例Examples

有关创建序列和使用 NEXT VALUE FOR 函数生成序列号的示例,请参阅序列号For examples of both creating sequences and using the NEXT VALUE FOR function to generate sequence numbers, see Sequence Numbers.

以下示例在名为 CountBy1 的架构中使用名为 Test 的序列。The following examples use a sequence named CountBy1 in a schema named Test. 将执行以下语句以创建 Test.CountBy1 序列。Execute the following statement to create the Test.CountBy1 sequence. 示例 C 和 E 使用 AdventureWorks2012AdventureWorks2012 数据库,因此,将在该数据库中创建 CountBy1 序列。Examples C and E use the AdventureWorks2012AdventureWorks2012 database, so the CountBy1 sequence is created in that database.

USE AdventureWorks2012 ;  
GO  
  
CREATE SCHEMA Test;  
GO  
  
CREATE SEQUENCE Test.CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  

A.A. 在 SELECT 语句中使用序列Using a sequence in a select statement

以下示例创建一个名为 CountBy1 的序列,每次使用该序列时将增加 1。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

B.B. 将变量设置为下一个序列值Setting a variable to the next sequence value

以下示例介绍了三种将变量设置为下一个序列号值的方法。The following example demonstrates three ways to set a variable to the next value of a sequence number.

DECLARE @myvar1 bigint = NEXT VALUE FOR Test.CountBy1  
DECLARE @myvar2 bigint ;  
DECLARE @myvar3 bigint ;  
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  

C.C. 将序列与排名窗口函数一起使用Using a sequence with a ranking window function

USE AdventureWorks2012 ;  
GO  
  
SELECT NEXT VALUE FOR Test.CountBy1 OVER (ORDER BY LastName) AS ListNumber,  
    FirstName, LastName  
FROM Person.Contact ;  
GO  

D.D. 在默认约束定义中使用 NEXT VALUE FOR 函数Using the NEXT VALUE FOR function in the definition of a default constraint

支持在默认约束定义中使用 NEXT VALUE FOR 函数。Using the NEXT VALUE FOR function in the definition of a default constraint is supported. 有关在 CREATE TABLE 语句中使用 NEXT VALUE FOR 的示例,请参阅序列号中的示例 C。For an example of using NEXT VALUE FOR in a CREATE TABLE statement, see Example CSequence Numbers. 以下示例使用 ALTER TABLE 将序列作为默认值添加到当前表中。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  

E.E. 在 INSERT 语句中使用 NEXT VALUE FOR 函数Using the NEXT VALUE FOR function in an INSERT statement

以下示例创建一个名为 TestTable 的表,然后使用 NEXT VALUE FOR 函数插入一行。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  
  

E.E. 将 NEXT VALUE FOR 函数与 SELECT … 一起使用INTOUsing the NEXT VALUE FOR function with SELECT ... INTO

以下示例使用 SELECT ... INTO 语句创建一个名为 Production.NewLocation 的表,然后使用 NEXT VALUE FOR 函数为每一行编号。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 AdventureWorks2012 ;   
GO  
  
SELECT NEXT VALUE FOR Test.CountBy1 AS LocNumber, Name   
    INTO Production.NewLocation  
    FROM Production.Location ;  
GO  
  
SELECT * FROM Production.NewLocation ;  
GO  

F.F. 授予执行 NEXT VALUE FOR 的权限Granting permission to execute NEXT VALUE FOR

以下示例为名为 AdventureWorks\Larry 的用户授予 UPDATE 权限以使用 Test.CounterSeq 序列执行 NEXT VALUE FORThe 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] ;  

另请参阅See Also

CREATE SEQUENCE (Transact-SQL) CREATE SEQUENCE (Transact-SQL)
ALTER SEQUENCE (Transact-SQL) ALTER SEQUENCE (Transact-SQL)
序列号Sequence Numbers