SQL Server 2016 brings a lot of new configuration options at the database level (DATABASE SCOPED CONFIGURATION) instead of at the instance level only. SQL Server 2017 adds to this list another new option. It’s called IDENTITY_CACHE and allows us to adjust if the IDENTITY values generator will use cache or not. If we will set the option off we will disable IDENTITY values caching and this prevents us from having gaps in the IDENTITY values chain in case of unexpected server restart or failure. It means that new IDENTITY values will be generated a little bit slower but the difference isn’t much significant and most of the standard applications like CRMs won’t show any difference.
IDENTITY cache setting isn’t still available in SQL Server Management Studio version 18.5.
Therefore we must set it manually in T-SQL.
We will check if IDENTITY_CACHE is available in our SQL Server edition first:
SELECT * FROM sys.database_scoped_configurations
Then we can switch this function ON|OFF:
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF | ON
A more detailed demonstration can be found at www.mssqltips.com.