Gap (pulo) no Identity, como evitar?

Desde o SQL Server 2012, o produto sofreu uma alteração no controle dos campos IDENTITY onde, logo na inicialização da instância, ele coloca em cache aproximadamente 1000 registros para agilizar o processo. Ao fazer isso, o identity é “pré-usado” internamente e o SQL se encarrega de entregar os próximos valores.

O ‘problema’ é que, ao reinicializar a instância, o cache é perdido e ele cria um gap desses registros não utilizados.

Veja:

use testedb
GO
CREATE TABLE IdentityTst (cod int identity(1,1), a bit default 0)
GO
insert into IdentityTst default values
GO 10 -- Inseração de 10 registros
select * from IdentityTst
GO
-- Reiniciar a instância
USE testedb
GO
insert into IdentityTst default values
GO 10 -- Inseração de 10 registros
select * from IdentityTst

Isso foi tema de diversas discussões no Fórum MSDN na época que eu participava dele… Por que? Porque muita gente usa o campo identity como uma chave de negócio (código do produto, por exemplo), e quando algo assim ocorre, aquela sequência “se perde” e “fica feio” no sistema esse pulo.

Existiu, inclusive um Connect Item para que isso fosse resolvido. E foi. Desde então, existe a trace flag 272, que faz com que essa reserva pare de ser executada e a sequência se mantenha:

PS > Enable-DbaTraceFlag -SqlInstance 172.28.111.31 -SqlCredential logan -TraceFlag 272
PS > docker exec -it hardcore_vaughan "powershell.exe"

PS C:\> Stop-Service MSSQLServer
PS C:\> Start-Service MSSQLServer
PS C:\> Exit

Mas notem que: Isso é uma alteração para toda a instância. Para fazer isso por banco, só a partir do SQL Server 2017, usando o SCOPED CONFIGURATION. Vejam:

PS C:\WINDOWS\system32> Disable-DbaTraceFlag -SqlInstance 172.28.111.31 -SqlCredential sa -TraceFlag 272


SourceServer : A718A98E8B21
InstanceName : MSSQLSERVER
SqlInstance  : A718A98E8B21
TraceFlag    : 272
Status       : Skipped
Notes        : Trace Flag is not running.
DateTime     : 2018-04-26 21:40:25.270

AVISO: [Disable-DbaTraceFlag][21:40:25] Trace Flag 272 is not currently running on 172.28.111.31


PS > Get-DbaTraceFlag -SqlInstance 172.28.111.31 -SqlCredential sa
[Get-DbaTraceFlag][21:41:22] No global trace flags enabled
PS > docker exec -it hardcore_vaughan "powershell.exe"

PS > Stop-Service MSSQLServer
PS > Start-Service MSSQLServer
PS > exit
PS >

SELECT * FROM SYS.database_scoped_configurations
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
SELECT * FROM SYS.database_scoped_configurations

Em tempo: Estou usando o dbatools.io via Posh para os cmdlets e o docker, ao invés de VM ou instalando localmente.

[]’s!