Problemas con "Huecos" o "Gaps" en Campos Identity - Posibles Motivos y Soluciones

Estimados colegas, el placer de saludarlos nuevamente.

  Cuántas veces nos hemos encontrado con el problema de "saltos" o "gaps" en el valor numérico de columnas definidas como "identity" en tablas de nuestras bases de datos?.

  Seguramente en más de una oportunidad hemos sido alertados por el equipo de desarrollo o por alertas propias sobre esta incidencia que le quita secuencialidad a nuestras tablas.

  Sobre este inconveniente trata el post del día de hoy. Asumo que todos sabemos lo que un campo "identity" signfica en una tabla y su funcionalidad y por que no, que también fue utilizado en una o más tablas de vuestros sistemas con la función de contar con un valor Unico y Secuencial, delegando estas característiscas en el motor de base de datos.

   Es así entonces que surje el primer punto a tener en cuenta y es el siguiente:

  "Un campo definido como identity NO GARANTIZA LA SECUENCIALIDAD de los valores del mismo"

 Así es colegas, tal como lo leen. Esto está expresado explícitamente por Microsoft en su documentación oficial. 

Si uds. requieren   por regla de negocios, generar secuencialidad. Por ejemplo si el campo identity sería el punto de partida de la numeración de la facturación de una empresa, deben recurrir a otros métodos alternativos de generación de Ids tales como Sequence no cacheado, (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15),  u otros generados a través de programación (eg guardar un id en una tabla isolada y sumarle 1 ante cada inserción).

  Pero por qué no podemos confiar la secuencialidad en un campo identity?

  Sencillamente, y aquí viene el primer motivo de un posible "gap" en una numeración identity, porque ante un rollback, los ids rollbackeados "se perderán" y pasarán a formar parte de un hueco de ids ya no disponibles para futuras inserciones..   Así pues, si las inserciones de dos ids cualquiera, digamos el  23 y 24,  se rollbackea, dichos ids estarán faltantes en nuestra numeración de facturas.

 Ok, pero uds. tal vez llegaron a este post buscando un motivo por el cual registraron un gap en la numeración identity y están seguros de que no se debió a un rollback....  Entonces  surge la pregunta...

 Hay otro motivo por el cual se pueden registrar gaps en una columna Identity?

 La respuesta es SI

 Si estás trabajando con un motor 2012 o superior y no has modificado el comportamiento por default del cacheo de identities en SQL Server, pueden ocurrir gaps muy grandes y no tan mensurables como el de un rollback de "x registros", ante estas situaciones:

• Caídas en General del Servicio de Motor de Base de Datos no Planificadas
• Failovers

 Efectivamente, y sin entrar en detalles técnicos, la gente de Microsoft con el noble fin de ganar performance comenzó, a partir de SQL 2012, a "cachear" los Identity como comportamiento por default de motor. 

Ventajas? ... supuestamente algunas de performance...(no comprobadas por mi nunca).

Desventajas?  las que devienen cacheados estos valores "core" en muchos sistemas de bases de datos cuyas lógicas de negocio dependen en muchos cosos de los mismos.

Cacheados = sin persistencia ante los eventos mencionados = Pérdida de correlatividad y Gaps a veces muy significativos.

En mi opinión esta fue una muy mala jugada de Microsoft quien no avisó con la debida firmeza sobre un cambio que complicó y sigue complicando la vida de muchos de quienes trabajan MSqlSrvr. Aún cuando se puede discutir sobre la mala praxys que implica el confiar la correlatividad y otros puntos importantes que hacen a la regla de negocios sobre este tipo de campos.

Pero.. hay modo de evitar este comportamiento que por defecto tiene el Sql Sever para que deje de cachear los Ids?

 Si lo hay veamos las opciones:

• Modificando el Identiy Cache a nivel Instancia de Motor de Base de Datos 

   Es es la única opción disponible si trabajás con las versiones 2012/2014 y 2016 

 

   Se trata de agregar el "TRACE flag 272" como parámetro de star-up 

   Los pasos son:

   1)  Ingresar al SQL Server Configuration Manager y editar properties

  2)  Agregar, (ver botón add), el flag "-T272" en la solapa "startup parameters" y aplicar cambios.

  

3) Restartear el servicio y para aplicar los cambios

 

• Modificando el Identiy a Nivel Base de Datos

     

 Esta opción está disponible a partir del SQL Server 2017 y es mi favorita puesto que pemite ser selectivo con la Base de Datos en la cual se desea implementar el setting y adicionalmente no requiere  del reinicio del motor una vez implementado el cambio.

   La sintaxys se debe aplicar en cada base de datos y es la siguiente:

    Use database;

  go

  ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

Amigos, esto es todo por ahora. Espero que el post les haya sido de utilidad y les prometo para un post adicional próximo una serie de scripts que les faciilitará el trabajo cuando de trabajar y controlar con tablas con campos identity se trate.

 Saludos cordiales desde Argentina, sigo esperando sus preguntas, sólo tengan paciencia y serán respondidas.