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]
GO

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

Not All Defaults Are Good

Nate Johnson rails against bad defaults in SQL Server: Your servers have many-core CPUs, right?  And you want SQL to utilize those cores to the best of its ability, distributing the many users’ workloads fairly amongst them, yes?  Damn right, you paid $3k or more per core in freaking licensing costs!  “OK”, says SQL Server, […]

Read More

Environmental Factors And SQL Server

Jeff Mlakar has a set of tips and tricks around SQL Server performance: Performance problems for a SQL Server based application are likely to be caused by environmental factors and not buggy code. Whether it is a configuration you can change in SQL Server, Windows Server, VMware, or the network it is likely the first […]

Read More

Categories