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

Desired State Configuration’s Local Configuration Manager

Jess Pomfret continues a series on Desired State Configuration in Powershell: Once we have crafted the perfect configuration and shipped it out to our target nodes, it’s time for the magic to happen. The MOF file that we created by executing our configuration is translated and enacted by the Local Configuration Manager (LCM) on each target […]

Read More

Storing An Encrypted Password In The Solr Configuration File

Jon Morisi shows us how to store an encrypted password in Solr’s configuration file, rather than storing the password in plaintext: The config file has a lot of options, in short this is where you configure a database connection string and reference your jdbc jar file. Full details are here.  By default any of the examples […]

Read More

Categories