NEXT VALUE FOR (Transact-SQL)

  • Artículo

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

Para obtener más información acerca de la creación y el uso de secuencias, vea Números de secuencia. Use sp_sequence_get_range para generar una reserva de un intervalo de números de secuencia.

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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

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 obtener más información, vea OVER (cláusula de 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 utiliza 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 cada 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 utilizar 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 usa el operador DISTINCT, UNION, UNION ALL, EXCEPT o INTERSECT.

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

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

  • En expresiones condicionales que usen 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 utilizar 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 predeterminadas 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 predeterminadas 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 predeterminada 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 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 utiliza 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 B de la cláusula OVER. Al utilizar 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 utilizar 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 permite con la función NEXT VALUE FOR si se utiliza en una instrucción SELECT o en una instrucción INSERT … SELECT …. La función NEXT VALUE FOR no se permite en instrucciones UPDATE o MERGE.

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

Metadatos

Para obtener 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 utiliza 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 mediante el 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.

Auditar

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

Ejemplos

Para obtener ejemplos de cómo crear secuencias y cómo utilizar la función NEXT VALUE FOR para generar los 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 AdventureWorks2012 , de modo que la secuencia CountBy1 se cree en esa base de datos.

USE AdventureWorks2012 ;
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 AdventureWorks2012 ;
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

Se admite el uso de la función NEXT VALUE FOR en la definición de una restricción predeterminada. Para obtener un ejemplo de cómo se usa NEXT VALUE FOR en una instrucción CREATE TABLE , vea el ejemplo CNú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 utiliza la instrucción SELECT … INTO para crear una tabla denominada Production.NewLocation y se utiliza la función NEXT VALUE FOR para numerar cada fila.

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.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 usando la secuencia Test.CounterSeq.

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

Vea también

Referencia

CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)

Conceptos

Números de secuencia