Disabling The Identity Cache

Andrew Pruski looks at a configuration setting in SQL Server 2017 which prevents SQL Server from bumping the next identity value on failover or restart:

Now we can check the data in the table: –

SELECT * FROM [dbo].[TestTable]

As expected, there’s a gap in the IDs. Why it jumps to 1002 is discussed in the connect item.

OK, now let’s try running the same code again but this time we will disable the identity cache.

This doesn’t eliminate gaps altogether; those can still happen upon rollback of a transaction.  This is reason #1 why you should not use identity columns as proper sequences.

Related Posts

Enabling Optimizer Fixes In SQL Server

Monica Rathbun explains that just upgrading a SQL Server database doesn’t enable optimizer changes: When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already stay at their pre-upgrade compatibility level, which […]

Read More

Comparing Server Configurations With Powershell

Andy Levy shows how to use a dbatools cmdlet, Get-DbaSpConfigure: I started with Get-DbaSpConfigure to retrieve the settings available from sp_configureas these were the most important to my comparison. I ran this against production as well as each of my test instances and saved the results of each to a variable. Because accessing my production instance requires either […]

Read More