Números de secuenciaSequence Numbers

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Una secuencia es un objeto enlazado a un esquema definido por el usuario que genera una secuencia de valores numéricos según la especificación con la que se creó la secuencia.A sequence is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. La secuencia de valores numéricos se genera en orden ascendente o descendente en un intervalo definido y puede repetirse cuando se solicite.The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested. Las secuencias, a diferencia de las columnas de identidad, no se asocian a tablas.Sequences, unlike identity columns, are not associated with tables. Una aplicación hace referencia a un objeto de secuencia para recibir su valor siguiente.An application refers to a sequence object to receive its next value. La aplicación controla la relación entre las secuencias y tablas.The relationship between sequences and tables is controlled by the application. Las aplicaciones de usuario pueden hacer referencia a un objeto de secuencia y coordinar las claves de valores entre varias filas y tablas.User applications can reference a sequence object and coordinate the values keys across multiple rows and tables.

Una secuencia se crea independientemente de las tablas utilizando la instrucción CREATE SEQUENCE .A sequence is created independently of the tables by using the CREATE SEQUENCE statement. Las opciones permiten controlar el incremento, los valores máximo y mínimo, el punto de inicio, la capacidad de reinicio automático y el almacenamiento en caché para aumentar el rendimiento.Options enable you to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance. Para obtener información acerca de las opciones, vea CREATE SEQUENCE.For information about the options, see CREATE SEQUENCE.

A diferencia de los valores de columnas de identidad que se generan cuando se insertan filas, una aplicación puede obtener el número de secuencia siguiente sin insertar la fila llamando a la función NEXT VALUE FOR .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. Se asigna el número de secuencia cuando se llama a NEXT VALUE FOR aun cuando el número nunca se inserta en una tabla.The sequence number is allocated when NEXT VALUE FOR is called even if the number is never inserted into a table. La función NEXT VALUE FOR se puede utilizar como valor predeterminado para una columna en una definición de tabla.The NEXT VALUE FOR function can be used as the default value for a column in a table definition. Use sp_sequence_get_range para obtener un rango de varios números de secuencia de una sola vez.Use sp_sequence_get_range to get a range of multiple sequence numbers at once.

Una secuencia se puede definir como cualquier tipo de datos enteros.A sequence can be defined as any integer data type. Si no se especifica el tipo de datos, una secuencia tiene como valor predeterminado bigint.If the data type is not specified, a sequence defaults to bigint.

Utilizar secuenciasUsing Sequences

Utilice secuencias en lugar de columnas de identidad en los siguientes escenarios:Use sequences instead of identity columns in the following scenarios:

  • La aplicación requiere un número antes de realizar la inserción en la tabla.The application requires a number before the insert into the table is made.

  • La aplicación requiere compartir una serie única de números entre varias tablas o varias columnas de una tabla.The application requires sharing a single series of numbers between multiple tables or multiple columns within a table.

  • La aplicación debe reiniciar la serie de números cuando se alcanza un número especificado.The application must restart the number series when a specified number is reached. Por ejemplo, después de asignar valores entre 1 y 10, la aplicación comienza de nuevo a asignar valores entre 1 y 10.For example, after assigning values 1 through 10, the application starts assigning values 1 through 10 again.

  • La aplicación requiere que los valores de secuencia se ordenen por otro campo.The application requires sequence values to be sorted by another field. La función NEXT VALUE FOR puede aplicar la cláusula OVER a la llamada a la función.The NEXT VALUE FOR function can apply the OVER clause to the function call. La cláusula OVER garantiza que los valores devueltos se generen en el orden de la cláusula OVER BY de la cláusula ORDER.The OVER clause guarantees that the values returned are generated in the order of the OVER clause's ORDER BY clause.

  • Una aplicación requiere que se asignen varios números al mismo tiempo.An application requires multiple numbers to be assigned at the same time. Por ejemplo, una aplicación necesita reservar cinco números secuenciales.For example, an application needs to reserve five sequential numbers. Al solicitar los valores de identidad, podrían producirse lagunas en la serie si se emitieron números simultáneamente para otros procesos.Requesting identity values could result in gaps in the series if other processes were simultaneously issued numbers. Al llamar a sp_sequence_get_range se pueden recuperar de una sola vez varios números de la secuencia.Calling sp_sequence_get_range can retrieve several numbers in the sequence at once.

  • Necesita cambiar la especificación de la secuencia, como, por ejemplo, el valor de incremento.You need to change the specification of the sequence, such as the increment value.

LimitacionesLimitations

A diferencia de las columnas de identidad, cuyos valores no se pueden cambiar, los valores de secuencia no se protegen automáticamente después de la inserción en la tabla.Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. Para evitar que se cambien los valores de secuencia, utilice un desencadenador de actualización en la tabla para revertir los cambios.To prevent sequence values from being changed, use an update trigger on the table to roll back changes.

La singularidad no se aplica automáticamente para los valores de la secuencia.Uniqueness is not automatically enforced for sequence values. La capacidad de reutilizar los valores de secuencia es por diseño.The ability to reuse sequence values is by design. Si es necesario que los valores de secuencia de una tabla sean únicos, cree un índice único en la columna.If sequence values in a table are required to be unique, create a unique index on the column. Si se requiere que los valores de secuencia de una tabla sean únicos en todo un grupo de tablas, cree desencadenadores para evitar los duplicados debidos a las instrucciones de actualización o al ciclo del número de secuenciaIf sequence values in a table are required to be unique throughout a group of tables, create triggers to prevent duplicates caused by update statements or sequence number cycling.

El objeto de secuencia genera los números según su definición, pero el objeto de secuencia no controla cómo se utilizan los números.The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. Los números de secuencia insertados en una tabla pueden tener lagunas cuando se revierte una transacción, cuando varias tablas comparten un objeto de secuencia o cuando los números de secuencia se asignan sin utilizarlos en tablas.Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables. Cuando se crea con la opción CACHE, un cierre inesperado, como un error de alimentación, puede perder los números de secuencia de la memoria caché.When created with the CACHE option, an unexpected shutdown, such as a power failure, can lose the sequence numbers in the cache.

Si hay varias instancias de la función NEXT VALUE FOR que especifica el mismo generador de secuencias dentro de una única instrucción Transact-SQLTransact-SQL , todas esas instancias devuelven el mismo valor para una fila determinada procesada por esa instrucción Transact-SQLTransact-SQL .If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQLTransact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQLTransact-SQL statement. Este comportamiento es coherente con el estándar ANSI.This behavior is consistent with the ANSI standard.

Los números de secuencia se generan fuera del ámbito de la transacción actual.Sequence numbers are generated outside the scope of the current transaction. Se utilizan tanto si la transacción que usa el número de secuencia se confirma como si se revierte.They are consumed whether the transaction using the sequence number is committed or rolled back. La validación de duplicados solo se produce una vez que un registro está totalmente relleno.Duplicate validation only occurs once a record is fully populated. Esto puede dar lugar a casos en que se use el mismo número para más de un registro durante la creación, pero luego se identifique como un duplicado.This can result in some cases where the same number is used for more than one record during creation, but then gets identified as a duplicate. Si ocurre esto y se han aplicado otros valores autonuméricos a sucesivos registros, esta situación puede dar lugar a una discrepancia entre los valores autonuméricos.If this occurs and other autonumber values have been applied to subsequent records, this can result in a gap between autonumber values.

Uso típicoTypical Use

Para crear un número de secuencia entero que se incremente en 1 de -2.147.483.648 a 2.147.483.647, utilice la siguiente instrucción.To create an integer sequence number that increments by 1 from -2,147,483,648 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    INCREMENT BY 1 ;  

Para crear un número de secuencia entero similar a una columna de identidad que se incrementa en 1 de 1 a 2.147.483.647, utilice la siguiente instrucción.To create an integer sequence number similar to an identity column that increments by 1 from 1 to 2,147,483,647, use the following statement.

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
  

Administrar secuenciasManaging Sequences

Para obtener información sobre las secuencias, consulte sys.sequences.For information about sequences, query sys.sequences.

EjemplosExamples

Encontrará más ejemplos en los temas CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL) y sp_sequence_get_range.There are additional examples in the topics CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL), and sp_sequence_get_range.

A.A. Usar un número de secuencia en una sola tablaUsing a sequence number in a single table

En el siguiente ejemplo se crea un esquema denominado Test, una tabla denominada Orders y una secuencia denominada CountBy1 y, después, se insertan filas en la tabla mediante la función NEXT VALUE FOR.The following example creates a schema named Test, a table named Orders, and a sequence named CountBy1, and then inserts rows into the table using the NEXT VALUE FOR function.

--Create the Test schema  
CREATE SCHEMA Test ;  
GO  
  
-- Create a table  
CREATE TABLE Test.Orders  
    (OrderID int PRIMARY KEY,  
    Name varchar(20) NOT NULL,  
    Qty int NOT NULL);  
GO  
  
-- Create a sequence  
CREATE SEQUENCE Test.CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
-- Insert three records  
INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;  
INSERT test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;  
INSERT test.Orders (OrderID, Name, Qty)  
    VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;  
GO  
  
-- View the table  
SELECT * FROM Test.Orders ;  
GO  

El conjunto de resultados es el siguiente.Here is the result set.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

B.B. Llamar a NEXT VALUE FOR antes de insertar una filaCalling NEXT VALUE FOR before inserting a row

Utilizando la tabla Orders creada en el ejemplo A, el siguiente ejemplo declara una variable denominada @nextIDy, a continuación, utiliza la función NEXT VALUE FOR para establecer la variable como el siguiente número de secuencia disponible.Using the Orders table created in example A, the following example declares a variable named @nextID, and then uses the NEXT VALUE FOR function to set the variable to the next available sequence number. Se supone que la aplicación realiza cierto procesamiento del pedido, como, por ejemplo, proporcionar al cliente el número de OrderID de su pedido potencial y, a continuación, valida el pedido.The application is presumed to do some processing of the order, such as providing the customer with the OrderID number of their potential order, and then validates the order. Con independencia de cuánto tiempo pueda llevar este procesamiento y de cuántos pedidos se agreguen durante el proceso, el número original se conserva para que lo utilice esta conexión.No matter how long this processing might take, or how many other orders are added during the process, the original number is preserved for use by this connection. Finalmente, la instrucción INSERT agrega el pedido a la tabla Orders .Finally, the INSERT statement adds the order to the Orders table.

DECLARE @NextID int ;  
SET @NextID = NEXT VALUE FOR Test.CountBy1;  
-- Some work happens  
INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (@NextID, 'Rim', 2) ;  
GO  
  

C.C. Usar un número de secuencia en varias tablasUsing a sequence number in multiple tables

En este ejemplo se supone que un proceso de supervisión de la línea de producción recibe una notificación de los eventos que se producen en el taller.This example assumes that a production-line monitoring process receives notification of events that occur throughout the workshop. Cada evento recibe un número EventID único que se incrementa de forma continua.Each event receives a unique and monotonically increasing EventID number. Todos los eventos utilizan el mismo número de secuencia EventID para que los informes que combinan todos los eventos puedan identificar cada evento de forma única.All events use the same EventID sequence number so that reports that combine all events can uniquely identify each event. Sin embargo, los datos de evento se almacenan en tres tablas diferentes, dependiendo del tipo de evento.However the event data is stored in three different tables, depending on the type of event. El ejemplo de código crea un esquema denominado Audit, una secuencia denominada EventCountery tres tablas, cada una de las cuales utiliza la secuencia EventCounter como valor predeterminado.The code example creates a schema named Audit, a sequence named EventCounter, and three tables which each use the EventCounter sequence as a default value. A continuación el ejemplo agrega las filas a las tres tablas y consulta los resultados.Then the example adds rows to the three tables and queries the results.

CREATE SCHEMA Audit ;  
GO  
CREATE SEQUENCE Audit.EventCounter  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
CREATE TABLE Audit.ProcessEvents  
(  
    EventID int PRIMARY KEY CLUSTERED   
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EventCode nvarchar(5) NOT NULL,  
    Description nvarchar(300) NULL  
) ;  
GO  
  
CREATE TABLE Audit.ErrorEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NULL,  
    ErrorNumber int NOT NULL,  
    EventDesc nvarchar(256) NULL  
) ;  
GO  
  
CREATE TABLE Audit.StartStopEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NOT NULL,  
    StartOrStop bit NOT NULL  
) ;  
GO  
  
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)   
    VALUES (248, 0) ;  
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)   
    VALUES (72, 0) ;  
INSERT Audit.ProcessEvents (EventCode, Description)   
    VALUES (2735,   
    'Clean room temperature 18 degrees C.') ;  
INSERT Audit.ProcessEvents (EventCode, Description)   
    VALUES (18, 'Spin rate threashold exceeded.') ;  
INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)   
    VALUES (248, 82, 'Feeder jam') ;  
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)   
    VALUES (248, 1) ;  
INSERT Audit.ProcessEvents (EventCode, Description)   
    VALUES (1841, 'Central feed in bypass mode.') ;  
-- The following statement combines all events, though not all fields.  
SELECT EventID, EventTime, Description FROM Audit.ProcessEvents   
UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents   
UNION SELECT EventID, EventTime,   
CASE StartOrStop   
    WHEN 0 THEN 'Start'   
    ELSE 'Stop'  
END   
FROM Audit.StartStopEvents  
ORDER BY EventID ;  
GO  
  

El conjunto de resultados es el siguiente.Here is the result set.

EventID EventTime Description

1 2009-11-02 15:00:51.157 Start

2 2009-11-02 15:00:51.160 Start

3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.

4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.

5 2009-11-02 15:00:51.173 Feeder jam

6 2009-11-02 15:00:51.177 Stop

7 2009-11-02 15:00:51.180 Central feed in bypass mode.

D.D. Generar números de secuencia repetidos en un conjunto de resultadosGenerating repeating sequence numbers in a result set

En el siguiente ejemplo se muestran dos características de los números de secuencia: recorrer y utilizar NEXT VALUE FOR en una instrucción SELECT.The following example demonstrates two features of sequence numbers: cycling, and using NEXT VALUE FOR in a select statement.

CREATE SEQUENCE CountBy5  
   AS tinyint  
    START WITH 1  
    INCREMENT BY 1  
    MINVALUE 1  
    MAXVALUE 5  
    CYCLE ;  
GO  
  
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;  
GO  

E.E. Generar números de secuencia para un conjunto de resultados mediante la cláusula OVERGenerating sequence numbers for a result set by using the OVER clause

En el ejemplo siguiente se utiliza la cláusula OVER para ordenar el conjunto de resultados por Name antes de agregar la columna de número de secuencia.The following example uses the OVER clause to sort the result set by Name before it adds the sequence number column.

USE AdventureWorks2012 ;  
GO  
  
CREATE SCHEMA Samples ;  
GO  
  
CREATE SEQUENCE Samples.IDLabel  
    AS tinyint  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  
WHERE Name LIKE '%nut%' ;  

F.F. Restablecer el número de secuenciaResetting the sequence number

El ejemplo E consumió los primeros 79 números de secuencia de Samples.IDLabel.Example E consumed the first 79 of the Samples.IDLabel sequence numbers. (Su versión de AdventureWorks2012 puede devolver un número de resultados diferente). Ejecute lo siguiente para consumir los 79 números de secuencia siguientes (del 80 al 158).(Your version of AdventureWorks2012 may return a different number of results.) Execute the following to consume the next 79 sequence numbers (80 though 158).

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  
WHERE Name LIKE '%nut%' ;  

Ejecute la instrucción siguiente para reiniciar la secuencia Samples.IDLabel .Execute the following statement to restart the Samples.IDLabel sequence.

ALTER SEQUENCE Samples.IDLabel  
RESTART WITH 1 ;  

Ejecute la instrucción SELECT de nuevo para comprobar que la secuencia Samples.IDLabel se ha reiniciado con el número 1.Execute the select statement again to verify that the Samples.IDLabel sequence restarted with number 1.

SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product  
WHERE Name LIKE '%nut%' ;  

G.G. Cambiar una tabla de identidad a secuenciaChanging a table from identity to sequence

En el siguiente ejemplo se crean un esquema y una tabla que contiene tres filas para el ejemplo.The following example creates a schema and table containing three rows for the example. A continuación el ejemplo agrega una nueva columna y quita la columna anterior.Then the example adds a new column and drops the old column.

-- Create a schema  
CREATE SCHEMA Test ;  
GO  
  
-- Create a table  
CREATE TABLE Test.Department  
    (  
        DepartmentID smallint IDENTITY(1,1) NOT NULL,  
        Name nvarchar(100) NOT NULL,  
        GroupName nvarchar(100) NOT NULL  
    CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED   
         (DepartmentID ASC)   
    ) ;  
GO  
  
-- Insert three rows into the table  
INSERT Test.Department(Name, GroupName)  
    VALUES ('Engineering', 'Research and Development');  
GO  
  
INSERT Test.Department(Name, GroupName)  
    VALUES ('Tool Design', 'Research and Development');  
GO  
  
INSERT Test.Department(Name, GroupName)  
    VALUES ('Sales', 'Sales and Marketing');  
GO  
  
-- View the table that will be changed  
SELECT * FROM Test.Department ;  
GO  
  
-- End of portion creating a sample table  
--------------------------------------------------------  
-- Add the new column that does not have the IDENTITY property  
ALTER TABLE Test.Department   
    ADD DepartmentIDNew smallint NULL  
GO  
  
-- Copy values from the old column to the new column  
UPDATE Test.Department  
    SET DepartmentIDNew = DepartmentID ;  
GO  
  
-- Drop the primary key constraint on the old column  
ALTER TABLE Test.Department  
    DROP CONSTRAINT [PK_Department_DepartmentID];  
-- Drop the old column  
ALTER TABLE Test.Department  
    DROP COLUMN DepartmentID ;  
GO  
  
-- Rename the new column to the old columns name  
EXEC sp_rename 'Test.Department.DepartmentIDNew',   
    'DepartmentID', 'COLUMN';  
GO  
  
-- Change the new column to NOT NULL  
ALTER TABLE Test.Department  
    ALTER COLUMN DepartmentID smallint NOT NULL ;  
-- Add the unique primary key constraint  
ALTER TABLE Test.Department  
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED   
         (DepartmentID ASC) ;  
-- Get the highest current value from the DepartmentID column   
-- and create a sequence to use with the column. (Returns 3.)  
SELECT MAX(DepartmentID) FROM Test.Department ;  
-- Use the next desired value (4) as the START WITH VALUE;  
CREATE SEQUENCE Test.DeptSeq  
    AS smallint  
    START WITH 4  
    INCREMENT BY 1 ;  
GO  
  
-- Add a default value for the DepartmentID column  
ALTER TABLE Test.Department  
    ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)   
        FOR DepartmentID;  
GO  
  
-- View the result  
SELECT DepartmentID, Name, GroupName  
FROM Test.Department ;   
-- Test insert  
INSERT Test.Department (Name, GroupName)  
    VALUES ('Audit', 'Quality Assurance') ;  
GO  
  
-- View the result  
SELECT DepartmentID, Name, GroupName  
FROM Test.Department ;  
GO  
  

Las instrucciones Transact-SQLTransact-SQL que utilizan SELECT * recibirán la nueva columna como última columna, no como primera.Transact-SQLTransact-SQL statements that use SELECT * will receive the new column as the last column instead of the first column. Si esto no es aceptable, debe crear una tabla completamente nueva, mover los datos a ella y, a continuación, volver a crear los permisos en la nueva tabla.If this is not acceptable, then you must create an entirely new table, move the data to it, and then recreate the permissions on the new table.

CREATE SEQUENCE (Transact-SQL)CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)ALTER SEQUENCE (Transact-SQL)

DROP SEQUENCE (Transact-SQL)DROP SEQUENCE (Transact-SQL)

IDENTITY (propiedad de Transact-SQL)IDENTITY (Property) (Transact-SQL)