Números Secuenciales en SQL: Guía Definitiva

En el vasto mundo de la gestión de bases de datos, la necesidad de generar números secuenciales es una tarea común y fundamental. Ya sea para asignar identificadores únicos a nuevos registros, numerar filas en un informe o detectar patrones en series de datos, SQL ofrece diversas herramientas potentes y flexibles para lograrlo. Este artículo explorará a fondo las principales estrategias y funciones disponibles en SQL Server, Azure SQL Database y Azure SQL Managed Instance para la creación y manipulación de números secuenciales, brindándote el conocimiento necesario para aplicar la solución más adecuada a tus necesidades.

Objetos SEQUENCE: IDs Autoincrementales y Más Allá

Un objeto SEQUENCE en SQL es una herramienta definida por el usuario y ligada a un esquema, diseñada específicamente para generar una secuencia de valores numéricos. A diferencia de las columnas IDENTITY, que están intrínsecamente ligadas a una tabla y generan valores automáticamente cuando se insertan filas, las secuencias son objetos independientes. Esto significa que una aplicación puede solicitar el siguiente número de una secuencia antes de siquiera insertar un registro, y esta relación entre la secuencia y las tablas es gestionada por la propia aplicación.

¿Por Qué Usar un Objeto SEQUENCE?

Los objetos SEQUENCE ofrecen una flexibilidad superior a las columnas IDENTITY en varios escenarios:

• Números antes de la Inserción: Si tu aplicación necesita obtener un número de identificación antes de que el registro sea insertado en la tabla (por ejemplo, para mostrarlo al usuario o para coordinar con otros sistemas), puedes llamar a la función NEXT VALUE FOR. El número se asigna en el momento de la llamada, incluso si la transacción se revierte o el número nunca se usa.
• Compartir Secuencias: Permiten compartir una única serie de números entre múltiples tablas o incluso entre múltiples columnas dentro de la misma tabla. Esto es ideal para sistemas que requieren identificadores únicos y globales a través de diferentes entidades.
• Ciclos de Números: Puedes configurar una secuencia para que reinicie su serie de números después de alcanzar un valor máximo definido, lo cual es útil para escenarios donde se necesitan series de números repetitivas (por ejemplo, 1 al 10, luego 1 al 10 de nuevo).
• Ordenación Controlada: La función NEXT VALUE FOR puede utilizar la cláusula OVER, lo que permite generar valores secuenciales en el orden especificado por una cláusula ORDER BY dentro de la consulta.
• Asignación de Múltiples Números: Si una aplicación necesita reservar un rango de números secuenciales a la vez (por ejemplo, cinco números consecutivos), puede usar el procedimiento almacenado sp_sequence_get_range. Esto evita los vacíos que podrían ocurrir con las columnas IDENTITY si otras transacciones insertaran datos simultáneamente.
• Modificación Flexible: Puedes alterar la especificación de una secuencia (como el valor de incremento) después de haberla creado, sin afectar la estructura de las tablas que la utilizan.

Creación y Uso Básico de Secuencias

La creación de un objeto SEQUENCE se realiza con la sentencia CREATE SEQUENCE. Puedes definir su tipo de dato (por defecto es bigint), el valor de inicio (START WITH), el incremento (INCREMENT BY), los valores mínimos y máximos (MINVALUE, MAXVALUE), y si debe ciclar (CYCLE).

-- Crear un esquema de prueba si no existe CREATE SCHEMA Test; GO -- Crear una secuencia básica que empieza en 1 e incrementa en 1 CREATE SEQUENCE Test.CountBy1 START WITH 1 INCREMENT BY 1; GO -- Crear una tabla de ejemplo CREATE TABLE Test.Orders ( OrderID INT PRIMARY KEY, Name VARCHAR(20) NOT NULL, Qty INT NOT NULL ); GO -- Insertar filas usando NEXT VALUE FOR 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 SELECT * FROM Test.Orders; GO

El resultado será:

OrderID Name Qty ------- ---- --- 1 Tire 2 2 Seat 1 3 Brake 1

Ejemplos Avanzados de Uso de SEQUENCE

Llamando a NEXT VALUE FOR Antes de Insertar

Este ejemplo muestra cómo obtener el siguiente valor de una secuencia y asignarlo a una variable antes de realizar la inserción, lo que permite a la aplicación usar el ID antes de que el registro exista en la base de datos.

DECLARE @NextID AS INT; SET @NextID = NEXT VALUE FOR Test.CountBy1; -- Supongamos que aquí la aplicación realiza algún procesamiento o validación INSERT Test.Orders (OrderID, Name, Qty) VALUES (@NextID, 'Rim', 2); SELECT * FROM Test.Orders; -- Verificar la nueva inserción GO

Usando una Secuencia en Múltiples Tablas

Las secuencias son ideales para generar IDs globales a través de diferentes tablas, asegurando una numeración única y cronológica para eventos o registros relacionados pero almacenados por separado.

CREATE SCHEMA Audit; GO CREATE SEQUENCE Audit.EventCounter AS INT START WITH 1 INCREMENT BY 1; GO CREATE TABLE Audit.ProcessEvents ( EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED, EventTime DATETIME DEFAULT (getdate()) NOT NULL, EventCode NVARCHAR (5) NOT NULL, Description NVARCHAR (300) NULL ); GO CREATE TABLE Audit.ErrorEvents ( EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED, EventTime DATETIME DEFAULT (getdate()) NOT NULL, EquipmentID INT NULL, ErrorNumber INT NOT NULL, EventDesc NVARCHAR (256) NULL ); GO CREATE TABLE Audit.StartStopEvents ( EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED, EventTime DATETIME DEFAULT (getdate()) NOT NULL, 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 threshold 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.'); 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 resultado mostrará eventos de diferentes tablas con IDs secuenciales únicos y ordenados por su generación.

Reiniciando una Secuencia

Puedes reiniciar una secuencia a un valor específico utilizando ALTER SEQUENCE. Esto es útil para restablecer la numeración o para corregir errores.

ALTER SEQUENCE Test.CountBy1 RESTART WITH 1 ; GO

Limitaciones de los Objetos SEQUENCE

• No Unicidad Automática: A diferencia de las columnas IDENTITY con una clave primaria, los valores de secuencia no garantizan la unicidad por sí solos. Si se requiere unicidad, se debe aplicar una restricción UNIQUE en la columna donde se insertan los valores.
• No Protegidos contra Cambios: Una vez insertados en una tabla, los valores de secuencia no están automáticamente protegidos de ser modificados. Se pueden usar triggers de actualización para revertir cambios si es necesario.
• Posibles Vacíos: Pueden ocurrir vacíos en la secuencia si las transacciones se revierten, si el objeto de secuencia es compartido por múltiples tablas, o si los números se asignan pero no se usan. La opción CACHE, si bien mejora el rendimiento, puede causar la pérdida de números no utilizados si hay un apagado inesperado del servidor.
• Generación Fuera de la Transacción: Los números de secuencia se generan fuera del ámbito de la transacción actual. Se consumen ya sea que la transacción se confirme o se revierta.

La Función ROW_NUMBER(): Numeración de Filas en Consultas

Mientras que los objetos SEQUENCE son ideales para generar identificadores persistentes, la función ROW_NUMBER() se utiliza para asignar un número entero secuencial a cada fila en el conjunto de resultados de una consulta. Es una función de ventana y, como tal, requiere la cláusula OVER.

Uso Básico de ROW_NUMBER()

La forma más sencilla de usar ROW_NUMBER() es con una cláusula OVER vacía, lo que numerará todas las filas del conjunto de resultados sin un orden particular (el orden dependerá del plan de ejecución).

SELECT ROW_NUMBER() OVER () AS row_num, article_code, article_name, branch, units_sold FROM Sales WHERE article_code IN ( 101, 102, 103 );

Profundizando: PARTITION BY y ORDER BY

La verdadera potencia de ROW_NUMBER() reside en su capacidad para trabajar con las cláusulas PARTITION BY y ORDER BY dentro de la cláusula OVER:

• PARTITION BY: Divide el conjunto de resultados en particiones (grupos) lógicas. ROW_NUMBER() se reinicia para cada nueva partición.
• ORDER BY: Define el orden en el que se asignan los números dentro de cada partición.

Por ejemplo, para numerar los registros por sucursal y ordenar por unidades vendidas de forma descendente:

SELECT ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units_sold DESC) AS row_num, article_code, article_name, branch, units_sold FROM Sales WHERE article_code IN ( 101, 102, 103 );

Esto produciría un resultado donde la numeración (row_num) se reinicia para cada 'branch', y dentro de cada 'branch', las filas se ordenan por 'units_sold' en orden descendente.

Casos de Uso de ROW_NUMBER()

Eliminar Duplicados

Una aplicación común de ROW_NUMBER() es identificar y eliminar registros completamente duplicados en una tabla. Se asigna un número de fila a cada grupo de duplicados, y luego se eliminan aquellos con un número mayor a 1.

-- Asumiendo una tabla 'Sales' con duplicados -- Agregamos una columna temporal para el número de fila ALTER TABLE Sales ADD COLUMN row_rank INTEGER; -- Llenamos la nueva columna, particionando por todas las columnas para identificar duplicados exactos INSERT INTO Sales (article_code, article_name, branch, units_sold, period, row_rank) SELECT article_code, article_name, branch, units_sold, period, ROW_NUMBER() OVER (PARTITION BY article_code, article_name, branch, units_sold, period ORDER BY (SELECT NULL)) AS rn FROM Sales; -- (SELECT NULL) es un truco para ORDER BY cuando el orden no importa pero es requerido -- Eliminar los duplicados (aquellos con row_rank > 1) DELETE FROM Sales WHERE row_rank > 1; -- Eliminar la columna temporal ALTER TABLE Sales DROP COLUMN row_rank;

Advertencia: Este enfoque puede duplicar temporalmente el tamaño de la tabla durante el proceso, lo que puede ser ineficiente para tablas muy grandes.

Crear Informes de Clasificación (Ranking)

Aunque ROW_NUMBER() puede usarse para clasificar, es importante entender sus limitaciones con los empates. Si dos o más elementos tienen el mismo valor en la columna de ordenación, ROW_NUMBER() les asignará números consecutivos diferentes, lo cual no es una clasificación verdadera.

Para clasificaciones donde los empates deben recibir el mismo rango, se prefieren las funciones de ventana RANK() y DENSE_RANK().

-- Ejemplo con RANK() para una clasificación correcta con empates SELECT seller_name, RANK() OVER (ORDER BY units_sold DESC) AS units_ranking, RANK() OVER (ORDER BY revenue DESC) AS revenue_ranking, RANK() OVER (ORDER BY profit DESC) AS profit_ranking FROM Sellers_2019;

RANK() asigna el mismo rango a los empates y salta el siguiente número en la secuencia (ej: 1, 1, 3). DENSE_RANK() también asigna el mismo rango a los empates pero no salta números (ej: 1, 1, 2).

Identificación de Patrones Secuenciales con LEAD y LAG

Para identificar patrones de números consecutivos o valores repetidos en una secuencia de datos, las funciones de ventana LEAD() y LAG() son extremadamente útiles. LEAD() permite acceder a una fila posterior a la actual en el conjunto de resultados, mientras que LAG() accede a una fila anterior.

Detectar Números Consecutivos Repetidos

Imagina que necesitas encontrar un número que se repita tres veces consecutivas en una tabla, ordenado por un ID.

SELECT DISTINCT num FROM ( SELECT num, LEAD(num, 1) OVER (ORDER BY id) AS next_num, LEAD(num, 2) OVER (ORDER BY id) AS next_2_num FROM table_name ) AS consecutive_nums WHERE num = next_num AND num = next_2_num;

En esta consulta, la subconsulta utiliza LEAD(num, 1) para obtener el valor del siguiente número y LEAD(num, 2) para obtener el valor del número dos filas más adelante, basándose en el orden de la columna id. La consulta externa filtra los resultados donde el número actual es igual a los dos siguientes, identificando así las secuencias de tres.

Tabla Comparativa: IDENTITY vs. SEQUENCE vs. ROW_NUMBER()

Preguntas Frecuentes (FAQ)

¿Cuál es la diferencia principal entre IDENTITY y SEQUENCE?

La principal diferencia es que IDENTITY es una propiedad de una columna de tabla que genera valores automáticamente al insertar filas en esa tabla específica. Un objeto SEQUENCE, por otro lado, es un objeto de base de datos independiente que genera números secuenciales que pueden ser utilizados por cualquier tabla o aplicación, incluso antes de que se realice una inserción. Las secuencias ofrecen más flexibilidad en cuanto a cómo y cuándo se obtienen los números.

¿Cuándo debo usar ROW_NUMBER() en lugar de un objeto SEQUENCE?

Usa ROW_NUMBER() cuando necesites numerar las filas de un conjunto de resultados de una consulta para fines de visualización, paginación, eliminación de duplicados o clasificación temporal. No uses ROW_NUMBER() si necesitas un identificador persistente que se almacene en la base de datos. Para eso, usarías una columna IDENTITY o un objeto SEQUENCE.

¿ROW_NUMBER() garantiza un orden específico?

Sí, ROW_NUMBER() garantiza un orden específico si se utiliza una cláusula ORDER BY dentro de su cláusula OVER(). Si no se especifica un ORDER BY, el orden en que se asignan los números no está garantizado y puede variar con cada ejecución de la consulta.

¿Los números de secuencia son siempre únicos?

Los números generados por un objeto SEQUENCE son únicos dentro de esa secuencia, a menos que se configure para ciclar (CYCLE). Sin embargo, si insertas esos números en una tabla, la unicidad en la tabla no está garantizada automáticamente a menos que la columna tenga una restricción UNIQUE o PRIMARY KEY.

¿Qué sucede si una transacción con un número de secuencia se revierte?

Los números de secuencia se generan fuera del ámbito de la transacción. Esto significa que si una aplicación solicita un número de secuencia y luego la transacción en la que se iba a usar ese número se revierte (ROLLBACK), el número ya se ha consumido y no se reutilizará. Esto puede provocar vacíos en la secuencia de números.

¿Se pueden perder números de secuencia con la opción CACHE?

Sí. Cuando se crea una secuencia con la opción CACHE, SQL Server pre-asigna un bloque de números de secuencia en memoria para mejorar el rendimiento. Si el servidor se apaga de forma inesperada (por ejemplo, por un corte de energía) antes de que todos los números en el caché hayan sido utilizados, esos números se perderán y no se reutilizarán, lo que creará vacíos en la secuencia.

¿Cómo puedo reiniciar un objeto SEQUENCE?

Puedes reiniciar un objeto SEQUENCE a un valor específico usando la sentencia ALTER SEQUENCE nombre_secuencia RESTART WITH nuevo_valor;.

¿Qué son las funciones de ventana en SQL?

Las funciones de ventana realizan un cálculo en un conjunto de filas que están relacionadas con la fila actual. A diferencia de las funciones de agregado que agrupan filas, las funciones de ventana devuelven un valor para cada fila sin reducir el número de filas en el resultado. La cláusula OVER() es lo que define el 'marco de la ventana' o el conjunto de filas sobre el que opera la función. ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD() y LAG() son ejemplos comunes de funciones de ventana.

Conclusión

La gestión de números secuenciales es un aspecto vital en el diseño y la implementación de bases de datos robustas. Desde los persistentes y flexibles objetos SEQUENCE, ideales para la generación de identificadores únicos y globales, hasta la versátil función ROW_NUMBER(), perfecta para la numeración dinámica de resultados de consultas y la manipulación de datos, SQL proporciona un conjunto completo de herramientas. Entender las diferencias y aplicaciones de cada una te permitirá elegir la solución más eficiente y adecuada para tus necesidades, optimizando tanto el rendimiento como la integridad de tus datos.

Si quieres conocer otros artículos parecidos a Números Secuenciales en SQL: Guía Definitiva puedes visitar la categoría Cálculos.