Identity SQL Server se incrementa en 1000

 

Buenas tardes queridos lectores, el día de hoy veremos lo que llamaremos un "bug" en SQL SERVER que tiene que ver con la identidad, es decir un campo Identity que de la nada se incrementa en mil, y no sabemos porque, pues aquí tendrán la respuesta.

En ocasiones podemos apreciar que el campo Identity en SQL SERVER puede aumentar en 1000 (mil) posiciones en un campo int o 10000 (diez mil) en un campo bigint, esto se da a partir de la versión 2012 de SQL SERVER y esto es porque SQL SERVER reserva en cache un espacio de estas posiciones, por lo que, en caso de reinicio estos valores se desperdician.

Veamos el caso del error considerando el siguiente código::

create database identidad

go

 

USE IDENTIDAD

GO

 

CREATE TABLE IDENTIDAD

(

ID INT PRIMARY KEY IDENTITY,

VALOR1 VARCHAR(MAX),

VALOR2 VARCHAR(MAX)

)

 

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('EMMANUEL', 'URIAS')

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR1', 'VALOR1')

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR2', 'VALOR2')

 

SELECT * FROM IDENTIDAD

Como vemos, primero generamos la base de datos, luego creamos una tabla que tiene el id y le asignamos la identidad a la llave primaria, posteriormente ingresamos algunos datos y listo, el resultado es el siguiente:

Hasta aquí todo correcto, pero que pasa si nos quedamos sin corriente eléctrica o la instancia se reinicia por alguna pantalla azul de Windows o por otras razones, pues bien, vamos a simular que la instancia se apaga, y de hecho utilizaremos un comando que sirve para apagar la instancia y usaremos el siguiente:

SHUTDOWN WITH NOWAIT

Shutdown with nowait

Con esto, la instancia se apaga y tendremos que volver a habilitar el servicio

Iniciar servicio SQL SERVER

Y nos volvemos a conectar al Manager Studio

Conectar al Management Studio

Ahora vamos a ingresar nuevamente datos en nuestra base de datos, en la misma tabla que lo hicimos hace unos instantes

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR3', 'VALOR3')

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR4', 'VALOR4')

 

SELECT * FROM IDENTIDAD

Como podemos ver, la identidad dio un brinco de 1000 (mil) posiciones debido al apagado de la instancia, pues bien, para solucionar este problema,  tenemos tres posibles soluciones.

1. Secuencias en SQL SERVER

Nuestra primera solución es quitar la identidad y utilizar en su lugar secuencias sin dejar caché de la siguiente manera:

CREATE SEQUENCE IDENTIDAD_ID AS int START WITH 1 NO CACHE

Para mayor información sobre secuencias puedes dar clic aqui Respuestas en Informática: Secuencias en SQL SERVER (respuestasit.com.mx)

Y a la tabla en el campo id le ponemos un constraint default apuntando hacia el valor que sigue de la secuencia

CREATE TABLE IDENTIDAD

(

ID INT PRIMARY KEY DEFAULT NEXT VALUE FOR IDENTIDAD_ID,

VALOR1 VARCHAR(MAX),

VALOR2 VARCHAR(MAX)

)

 

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('EMMANUEL', 'URIAS')

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR1', 'VALOR1')

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR2', 'VALOR2')

Ahora si apagamos la instancia e ingresamos los otros valores, ya no tendremos el problema.

SHUTDOWN WITH NOWAIT

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR3', 'VALOR3')

INSERT INTO IDENTIDAD (VALOR1, VALOR2) VALUES ('VALOR4', 'VALOR4')

SELECT * FROM IDENTIDAD

2. Arrancar el servicio de SQL SERVER con parámetro 272

Otra opción para corregir el problema es iniciar el servicio de SQL SERVER con el parámetro -T272 de la siguiente manera:

Al agregar ese parámetro y reiniciar la instancia, se elimina el cache del identity, esto sería a nivel de instancia.

3. Cambiar el alcance (scope) de la base de datos

La tercer solución que tenemos, parece la más sencilla y es deshabilitar el cache de la identidad, mediante la configuración de scope de la base de datos, esto sería a nivel de base de datos y sería con la siguiente sentencia:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

Con esto tendríamos solucionado el problema de los vacíos en la identidad. Esper les haya gustado y servido el post, los invito a dar clic en algún anuncio patrocinado.

Saludos

Referencias

SQL Server introduce un salto de 1000 posiciones en datos de tipo Identity | ramoncarrasco.es

Respuestas en Informática: Secuencias en SQL SERVER (respuestasit.com.mx)

James' SQL Footprint: Gap issue in SQL Server 2012 IDENTITY column (jamessql.blogspot.com)

How to enable and disable the Identity Cache in SQL Server 2017 (sqlshack.com)