viernes 27 septiembre 2024

Cómo generar secuencias numéricas en SQL Server

¿Qué es `SEQUENCE` y para qué se usa?

SEQUENCE es un objeto de base de datos que genera números en un orden específico, basándose en las propiedades definidas por el usuario, como el valor inicial, el incremento y el límite. Puedes crear secuencias para generar identificadores únicos, números de serie, números de facturas o cualquier secuencia de valores que necesites controlar manualmente.

Diferencias entre `SEQUENCE` e `IDENTITY`

SEQUENCE: Es un objeto independiente que se puede utilizar en múltiples tablas y se puede reiniciar o modificar en cualquier momento.
IDENTITY: Es una propiedad de columna específica que se utiliza para generar valores automáticos dentro de una sola tabla y no se comparte con otras.

Ventajas de `SEQUENCE`

Se puede reutilizar en diferentes tablas.
Admite reinicio (RESTART), ajuste del valor actual y control preciso del rango.
Soporta incrementos negativos, positivos o personalizados.
Permite configuraciones más complejas como ciclos (repetir la secuencia) y límites personalizados.

Sintaxis de `SEQUENCE`

CREATE SEQUENCE [schema_name.]sequence_name
    [ AS [type] ]
    [ START WITH start_value ]
    [ INCREMENT BY increment_value ]
    [ { MINVALUE min_value | NO MINVALUE } ]
    [ { MAXVALUE max_value | NO MAXVALUE } ]
    [ CYCLE | NO CYCLE ]
    [ CACHE [ cache_size | NO CACHE ] ];

Parámetros principales

schema_name: Esquema al que pertenece la secuencia (por ejemplo, dbo).
sequence_name: Nombre de la secuencia.
AS type: Tipo de datos de la secuencia (INT, BIGINT, SMALLINT, etc.).
START WITH: Valor inicial de la secuencia.
INCREMENT BY: Valor de incremento (+1, +10, -1, etc.).
MINVALUE y MAXVALUE: Definen los límites de la secuencia.
CYCLE: Permite que la secuencia se reinicie al alcanzar el límite.
CACHE: Mejora el rendimiento almacenando valores en memoria para su uso rápido.

Ejemplos de uso de `SEQUENCE`

Crear una secuencia simple

Supongamos que deseas crear una secuencia para números de factura, comenzando en 1000 e incrementándose de 1 en 1:

CREATE SEQUENCE dbo.NumeroFactura
    START WITH 1000
    INCREMENT BY 1;

Cada vez que se llame a esta secuencia, generará un número de factura comenzando en 1000 y aumentando de uno en uno.

Generar un valor de secuencia

Para obtener el siguiente valor de la secuencia NumeroFactura, usa NEXT VALUE FOR:

SELECT NEXT VALUE FOR dbo.NumeroFactura AS SiguienteNumero;

Resultado:

SiguienteNumero
----------------
1000

La siguiente vez que ejecutes el comando, el valor será 1001, y así sucesivamente.

Usar la secuencia en una tabla

Puedes utilizar SEQUENCE dentro de una instrucción INSERT para generar automáticamente un número de serie:

INSERT INTO Ventas (NumeroFactura, Producto, Cantidad)
VALUES (NEXT VALUE FOR dbo.NumeroFactura, 'Laptop', 2);

Esto inserta un nuevo registro con el siguiente número de factura (1000, 1001, etc.) generado por la secuencia.

Crear una secuencia con valores negativos y ciclo

Si necesitas una secuencia que comience en 0, disminuya de 1 en 1 y reinicie al llegar a -10:

CREATE SEQUENCE dbo.SecuenciaNegativa
    START WITH 0
    INCREMENT BY -1
    MINVALUE -10
    MAXVALUE 0
    CYCLE;

La secuencia generará los valores: 0, -1, -2, ..., -10, y luego reiniciará a 0 al alcanzar el límite.

Usar la secuencia para definir valores predeterminados

Puedes definir una columna con un valor predeterminado usando la secuencia:

CREATE TABLE Ordenes (
    OrdenID INT DEFAULT NEXT VALUE FOR dbo.NumeroFactura,
    Cliente NVARCHAR(100),
    Fecha DATETIME DEFAULT GETDATE()
);

Al insertar un nuevo registro sin especificar OrdenID, la secuencia generará automáticamente el número para la columna.

Reiniciar una secuencia (`RESTART`)

Si deseas reiniciar la secuencia a un valor específico, utiliza:

ALTER SEQUENCE dbo.NumeroFactura RESTART WITH 2000;

Esto reinicia la secuencia para que el próximo valor generado sea 2000.

Eliminar una secuencia

Si ya no necesitas la secuencia, puedes eliminarla con:

DROP SEQUENCE dbo.NumeroFactura;

Esto elimina la secuencia del esquema dbo.

Usar la secuencia en una cláusula `SELECT` con múltiples registros

Puedes aplicar la secuencia en un conjunto de resultados, por ejemplo, al seleccionar registros de una tabla:

SELECT NEXT VALUE FOR dbo.NumeroFactura AS NumeroSerie, NombreProducto
FROM Productos;

Esto asigna un número de serie único a cada fila seleccionada de la tabla Productos.

Consideraciones importantes

Persistencia y rendimiento: Al usar CACHE, la secuencia almacena valores en memoria, mejorando el rendimiento, pero puede perder valores en caso de falla del servidor. NO CACHE asegura que todos los valores se mantengan, pero puede ser más lento.
Multihilo y concurrencia: SEQUENCE es seguro para entornos concurrentes, pero si muchas transacciones solicitan un valor simultáneamente, podrían saltarse números si alguna transacción se cancela.
Control de rangos: Si se define un MAXVALUE y se llega a este límite sin CYCLE, la secuencia fallará al generar un nuevo valor.

Referencia oficial

Para más información sobre SEQUENCE, consulta la documentación oficial de Microsoft.

Etiquetas:

sql server sql bases de datos

Creado por:

Jorge García

Fullstack developer

Cómo generar secuencias numéricas en SQL Server

¿Qué es SEQUENCE y para qué se usa?

Diferencias entre SEQUENCE e IDENTITY

Ventajas de SEQUENCE

Sintaxis de SEQUENCE