Porque SQL Server se salto 1000 IDs

Posted on Posted in SQL Server

Hace unos meses estaba haciendo pruebas en una aplicación móvil que desarrollaba y me di cuenta que SQL Server se salto 1000 IDs, la app se conectaba a un servicio WCF, el cual insertaba registros en una tabla común y corriente, con una llave primaria autoincrementable y algunas llaves foráneas a otras tablas, las pruebas las hacia día con día conforme iba desarrollando una nueva etapa del proceso de la aplicación.

El problema: SQL Server se salto 1000 IDs

Un día al hacer pruebas me percate de que el ID autoincrementable se saltaba 1000 números en algunas ocasiones, por ejemplo:

  • Del ID 22 al 1022
  • Del ID 168 al 1168

Porque SQL Server se salto 1000 IDs?

Pensaba que de alguna manera había creado algún ciclo que trataba de insertar algún registro una y otra vez sin éxito, pero no había ningún indicio de que así fuera, ninguna excepción lanzada, ningún mensaje de error, etc, etc.

La causa: SQL Server pre-asigna 1000 IDs para mejorar el rendimiento

Así que después de una búsqueda rápida, resulta que a partir del SQL Server 2012 en adelante agregaron la mejora de pre-asignar una cantidad de ID autoincrementables (1,000 en mi caso, parece que para BIGINT serian 10,000), que en caso de crear un nuevo registro se le daría uno de estos ID pre-asignados, mejorando así la velocidad de las inserciones.

Pero el problema viene cuando el motor se reinicia, se cae o pasa algo que no se esperaba, en esos casos, esos ID que se pre-asignaron y no se usaron en un registro se perderán, por lo tanto  el motor empezara a pre-asignar del ultimo ID en tu tabla mas 1,000 (o 10,000).

Para recrear el bug la funcionalidad necesitarías crear una tabla

CREATE TABLE [dbo].[SaltosEnTabla](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID_Manual] INT NOT NULL
) ON [PRIMARY]

Insertar 5 registros

INSERT INTO SaltosEnTabla VALUES (1)
INSERT INTO SaltosEnTabla VALUES (2)
INSERT INTO SaltosEnTabla VALUES (3)
INSERT INTO SaltosEnTabla VALUES (4)
INSERT INTO SaltosEnTabla VALUES (5)

Reiniciar el servidor, y luego insertar otros 5 registros

INSERT INTO SaltosEnTabla VALUES (6)
INSERT INTO SaltosEnTabla VALUES (7)
INSERT INTO SaltosEnTabla VALUES (8)
INSERT INTO SaltosEnTabla VALUES (9)
INSERT INTO SaltosEnTabla VALUES (10)

Y al final traer los registros

SELECT * FROM SaltosEnTabla

Para ver que los primeros 5 tienen los ID del 1 – 5 y los últimos 5 del 1001 – 1005

SQL Server se salto 1000 ids

La solución: Decirle explicitamente al motor de SQL Server que no pre-asigne IDs

Hay dos formas de evitar que SQL Server se salte 1000 IDs, empleados de Microsoft escribieron las dos soluciones en Microsoft Connect, las cuales serian

  1. Usar la bandera 272
  2. Usar un generador de secuencia con la propiedad de NO CACHE

Con características como esta quien necesita bugs :v