NEXT VALUE FOR (Transact-SQL)

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Genera un número de secuencia a partir del objeto de secuencia especificado.

Para más información sobre cómo crear y usar secuencias, vea Números de secuencia. Use sp_sequence_get_range para generar una reserva de un intervalo de números de secuencia.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 (12.x) y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

database_name
Nombre de la base de datos que contiene el objeto de secuencia.

schema_name
Nombre del esquema que contiene el objeto de secuencia.

sequence_name
Nombre del objeto de secuencia que genera el número.

over_order_by_clause
Determina el orden en el que se asigna el valor de secuencia a las filas de una partición. Para más información, vea Cláusula OVER (Transact-SQL).

Tipos de valor devuelto

Devuelve un número utilizando el tipo de la secuencia.

Comentarios

La función NEXT VALUE FOR se puede usar en procedimientos almacenados y en desencadenadores.

Cuando la función NEXT VALUE FOR se usa en una consulta o restricción predeterminada, si se usa más de una vez el mismo objeto de secuencia, o si se usa el mismo objeto de secuencia en la instrucción que proporciona los valores y en una restricción predeterminada que se está ejecutando, se devolverá el mismo valor para todas las columnas que hagan referencia a la misma secuencia dentro de una fila del conjunto de resultados.

La función NEXT VALUE FOR es no determinista y solo se permite en contextos donde se define bien el número de valores de secuencia generados. A continuación se encuentra la definición de cuántos valores se utilizarán para cada objeto de secuencia al que se hace referencia en una instrucción determinada:

  • SELECT: por cada objeto de secuencia al que se hace referencia, se genera un valor nuevo una vez por fila en el resultado de la instrucción.

  • INSERT ... VALUES: por cada objeto de secuencia al que se hace referencia, se genera un valor nuevo una vez por cada fila insertada en la instrucción.

  • UPDATE: por cada objeto de secuencia al que se hace referencia, se genera un valor nuevo para cada fila que actualiza la instrucción.

  • Instrucciones de procedimiento (como DECLARE, SET, etc.): por cada objeto de secuencia al que se hace referencia, se genera un valor nuevo para cada instrucción.

Limitaciones y restricciones

La función NEXT VALUE FOR no se puede usar en las siguientes situaciones:

  • Cuando una base de datos está en modo de solo lectura.

  • Como argumento a una función con valores de tabla.

  • Como argumento a una función de agregado.

  • En subconsultas que contienen expresiones de tabla comunes y tablas derivadas.

  • En vistas, en funciones definidas por el usuario o en columnas calculadas.

  • En una instrucción que use los operadores DISTINCT, UNION, UNION ALL, EXCEPT o INTERSECT.

  • En una instrucción que use la cláusula ORDER BY, a menos que se use NEXT VALUE FOR ... OVER (ORDER BY ...).

  • En las siguientes cláusulas: FETCH, OVER, OUTPUT, ON, PIVOT, UNPIVOT, GROUP BY, HAVING, COMPUTE, COMPUTE BY o FOR XML.

  • En expresiones condicionales que usan CASE, CHOOSE, COALESCE, IIF, ISNULL o NULLIF.

  • En una cláusula VALUES que no forme parte de una instrucción INSERT.

  • En la definición de una restricción CHECK.

  • En la definición de una regla u objeto predeterminado. (Se puede utilizar en una restricción predeterminada).

  • Como valor predeterminado de un tipo de tabla definido por el usuario.

  • En una instrucción que use TOP, OFFSET o cuando se establece la opción ROWCOUNT.

  • En la cláusula WHERE de una instrucción.

  • En una instrucción MERGE (salvo cuando la función NEXT VALUE FOR se use en una restricción predeterminada en la tabla de destino y el valor predeterminado se use en la instrucción CREATE de la instrucción MERGE).

Utilizar un objeto de secuencia en una restricción predeterminada

Al usar la función NEXT VALUE FOR en una restricción predeterminada, se aplican las siguientes reglas:

  • Se puede hacer referencia a un objeto de secuencia único desde restricciones Default en varias tablas.

  • La tabla y el objeto de secuencia deben residir en la misma base de datos.

  • El usuario que agrega la restricción predeterminada debe tener el permiso REFERENCES en el objeto de secuencia.

  • No se puede quitar un objeto de secuencia al que se haga referencia desde una restricción predeterminada antes de quitar la restricción predeterminada.

  • Se devuelve el mismo número de secuencia para todas las columnas de una fila si varias restricciones Default utilizan el mismo objeto de secuencia, o si el mismo objeto de secuencia se utiliza en la instrucción que proporciona los valores y en una restricción Default que se esté ejecutando.

  • Las referencias a la función NEXT VALUE FOR de una restricción predeterminada no pueden especificar la cláusula OVER.

  • Se puede modificar un objeto de secuencia al que se haga referencia en una restricción predeterminada.

  • En el caso de las instrucciones INSERT ... SELECT o INSERT ... EXEC en las que los datos que se van a insertar procedan de una consulta con una cláusula ORDER BY, los valores que devuelva la función NEXT VALUE FOR se generarán en el orden especificado por la cláusula ORDER BY.

Utilizar un objeto de secuencia con una cláusula OVER ORDER BY

La función NEXT VALUE FOR permite generar valores de secuencia ordenados aplicando la cláusula OVER a la llamada a NEXT VALUE FOR. Si usa la cláusula OVER, el usuario tiene la garantía de que los valores que se devuelven se generan en el orden de la subcláusula ORDER BY de la cláusula OVER. Al usar la función NEXT VALUE FOR con la cláusula OVER, se aplican las siguientes reglas adicionales:

  • Si hay varias llamadas a la función NEXT VALUE FOR para el mismo generador de secuencias en una única instrucción, todas deben usar la misma definición de la cláusula OVER.

  • Si hay varias llamadas a la función NEXT VALUE FOR que hacen referencia a distintos generadores de secuencias en una única instrucción, pueden tener distintas definiciones de la cláusula OVER.

  • Una cláusula OVER aplicada a la función NEXT VALUE FOR no admite la subcláusula PARTITION BY.

  • Si todas las llamadas a la función NEXT VALUE FOR de una instrucción SELECT especifican la cláusula OVER, se puede usar una cláusula ORDER BY en la instrucción SELECT.

  • La cláusula OVER se admite con la función NEXT VALUE FOR si se usa en una instrucción SELECT o una instrucción INSERT ... SELECT .... No se puede usar la cláusula OVER con la función NEXT VALUE FOR en las instrucciones UPDATE ni MERGE.

  • Si otro proceso está teniendo acceso al objeto de secuencia a la vez, los números devueltos podrían tener lagunas.

Metadatos

Para más información sobre las secuencias, consulte la vista de catálogo sys.sequences.

Seguridad

Permisos

Requiere el permiso UPDATE en el objeto de secuencia o el esquema de la secuencia. Para obtener un ejemplo de concesión de permisos, vea el ejemplo F más adelante en este tema.

Encadenamiento de propiedad

Los objetos de secuencia son compatibles con el encadenamiento de propiedad. Si el objeto de secuencia tiene el mismo propietario que el procedimiento almacenado, desencadenador o tabla que hace la llamada (y un objeto de secuencia como restricción predeterminada), no se necesita ninguna comprobación de permiso en el objeto de secuencia. Si el objeto de secuencia no pertenece al mismo propietario que el procedimiento almacenado, desencadenador o tabla que hace la llamada, se requiere una comprobación de permiso en el objeto de secuencia.

Cuando la función NEXT VALUE FOR se usa como valor predeterminado en una tabla, los usuarios deben tener el permiso INSERT en la tabla y el permiso UPDATE en el objeto de secuencia para insertar datos por medio del valor predeterminado.

  • Si la restricción predeterminada tiene el mismo propietario que el objeto de secuencia, no es necesario ningún permiso en el objeto de secuencia cuando se llama a la restricción predeterminada.

  • Si la restricción predeterminada y el objeto de secuencia no son propiedad del mismo usuario, se necesitan permisos en el objeto de secuencia aunque la llamada se haga mediante la restricción predeterminada.

Auditoría

Para auditar la función NEXT VALUE FOR supervise SCHEMA_OBJECT_ACCESS_GROUP.

Ejemplos

Para ver ejemplos de cómo crear secuencias y cómo usar la función NEXT VALUE FOR para generar números de secuencia, vea Números de secuencia.

En los siguientes ejemplos se utiliza una secuencia denominada CountBy1 en un esquema denominado Test. Ejecute la siguiente instrucción para crear la secuencia Test.CountBy1. En los ejemplos C y E se utiliza la base de datos AdventureWorks2022, de modo que la secuencia CountBy1 se cree en esa base de datos.

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

A. Usar una secuencia en una instrucción SELECT

En el siguiente ejemplo se crea una secuencia denominada CountBy1 que aumenta en uno cada vez que se utiliza.

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

El conjunto de resultados es el siguiente:

FirstUse  
1  
 
SecondUse  
2

B. Establecer una variable en el siguiente valor de la secuencia

En el ejemplo siguiente se muestran tres maneras de establecer una variable en el siguiente valor de un número de secuencia.

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. Usar una secuencia con una función de ventana de categoría

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

D. Usar la función NEXT VALUE FOR en la definición de una restricción predeterminada

La función NEXT VALUE FOR se puede usar en la definición de una restricción predeterminada. Para ver un ejemplo de uso de NEXT VALUE FOR en una instrucción CREATE TABLE, vea el ejemplo C de Números de secuencia. En el siguiente ejemplo se utiliza ALTER TABLE para agregar una secuencia como valor predeterminado a una tabla actual.

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. Usar la función NEXT VALUE FOR en una instrucción INSERT

En el siguiente ejemplo se crea una tabla denominada TestTable y, a continuación, se utiliza la función NEXT VALUE FOR para insertar una fila.

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. Usar la función NEXT VALUE FOR con SELECT ... INTO

En el siguiente ejemplo se usa la instrucción SELECT ... INTO para crear una tabla denominada Production.NewLocation y se usa la funciónNEXT VALUE FOR para numerar cada fila.

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

F. Conceder permiso para ejecutar NEXT VALUE FOR

En el siguiente ejemplo se concede el permiso UPDATE a un usuario denominado AdventureWorks\Larry para ejecutar NEXT VALUE FOR con Test.CounterSeq.

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

Consulte también

CREATE SEQUENCE (Transact-SQL)
ALTER SEQUENCE (Transact-SQL)
Números de secuencia