Por qué Identity y Sequence dejan huecos en SQL Server

Que falte un valor identity no demuestra que alguien haya borrado una fila. SQL Server puede asignar un número a una inserción que luego se revierte y no deshacer esa asignación. Interpretar identity como un contador sin huecos genera falsas alarmas y puede llevar a reutilizar identificadores de forma peligrosa.

Una clave técnica responde a "qué fila es". Un número del negocio puede representar la posición de un documento en un proceso de emisión. Son necesidades con ciclos de vida distintos. Separarlas evita que el funcionamiento interno del almacenamiento determine accidentalmente la numeración comercial.

Observar la asignación antes del commit

El ejemplo usa una tabla temporal y conserva una primera fila confirmada para mostrar claramente las asignaciones.

CREATE TABLE #Tickets (
    TicketId int IDENTITY(1,1) PRIMARY KEY,
    Note nvarchar(80) NOT NULL
);
INSERT #Tickets(Note) VALUES (N'First committed row');
BEGIN TRANSACTION;
INSERT #Tickets(Note) VALUES (N'This row is rolled back');
ROLLBACK TRANSACTION;
INSERT #Tickets(Note) VALUES (N'Next committed row');
SELECT TicketId, Note FROM #Tickets ORDER BY TicketId;
DROP TABLE #Tickets;

Los identificadores restantes son 1 y 3. El valor 2 correspondía a la inserción revertida. No falta ninguna fila confirmada: la transacción eliminó correctamente su escritura y el asignador continuó avanzando.

IDENTITY pertenece a una tabla. SEQUENCE es un objeto de esquema independiente que puede entregar valores antes de insertar y compartirlos entre tablas. Esa flexibilidad sirve cuando la clave debe conocerse anticipadamente, pero también hace normales las asignaciones sin uso. Los valores de secuencia no se recuperan mediante rollback.

La caché mejora la eficiencia y puede crear otros huecos cuando un apagado inesperado pierde valores reservados y aún no usados. Desactivarla reduce esa causa concreta, pero no recupera números de operaciones revertidas o abandonadas. NO CACHE no garantiza numeración continua.

Separa generación y unicidad. Usa una clave primaria o restricción única para el identificador almacenado. Cambiar la semilla, insertar identidades explícitas o usar secuencias cíclicas puede causar colisiones si el esquema no las impide. Esas acciones requieren migraciones controladas, no reparaciones periódicas de huecos.

Recuperar las claves realmente generadas

Consultar MAX(Id) y sumar uno no predice con seguridad el siguiente valor. Otra sesión puede insertar entre lectura y escritura. La diferencia entre el máximo y el número de filas tampoco cuenta de forma fiable los registros borrados.

Para una inserción de una fila, SCOPE_IDENTITY obtiene la última identidad del ámbito actual. @@IDENTITY puede devolver una creada por un desencadenador en otro ámbito. Para varias filas, OUTPUT inserted.Id devuelve las claves reales, pero no debes suponer que su orden coincide con el de entrada. Mantén una correlación explícita.

Los valores de OUTPUT no prueban que la transacción completa se confirmara. Un error posterior o un rollback puede invalidar la escritura. Las notificaciones externas deben seguir a la finalización confirmada del negocio y contemplar resultados de commit desconocidos.

El orden de las identidades tampoco es necesariamente el de confirmación. Una transacción puede obtener un número menor y terminar después de otra con número mayor. Usa una marca temporal explícita y un criterio de ordenación definido para informes; para consumidores que no deben perder cambios, utiliza captura de cambios adecuada.

Planificar capacidad y números del negocio

Esta consulta muestra columnas identity y sus últimos valores asignados.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       name AS ColumnName,
       TYPE_NAME(user_type_id) AS DataType,
       seed_value, increment_value, last_value
FROM sys.identity_columns
ORDER BY SchemaName, TableName;

Supervisa el rango restante según tipo, semilla, dirección del incremento y velocidad de asignación. Una identidad int positiva tiene un límite finito y las inserciones fallidas también consumen capacidad. Borrar filas antiguas no aleja ese límite.

Pasar de int a bigint puede afectar claves externas, índices secundarios, parámetros, exportaciones y tipos de la aplicación. Planifica antes del agotamiento, no como un cambio urgente de columna.

Si el negocio necesita una secuencia documental controlada, asigna ese número en el paso correcto de emisión, guárdalo separado y define las cancelaciones. Un contador transaccional puede serializar la asignación y limitar el rendimiento. Dividirlo por cliente, categoría o periodo solo sirve si la regla lo permite.

Prueba emisión concurrente, cancelación, reversión y reintentos cerca de COMMIT. La garantía útil es una política explicada y aplicada, no la apariencia de números consecutivos en una columna que nunca prometió eso.

Referencias técnicas: Microsoft Learn: IDENTITY property · Microsoft Learn: CREATE SEQUENCE · Microsoft Learn: OUTPUT clause.