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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 5 6 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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 > |
|
1 2 3 4 5 |
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!