Azure SQL database Indentity column auto increment jump 1000

Question

we have an azure database of members the id column value is going to be assigned to physical object i.e.cards. so the user has a card with a member id that corresponds to the id of their record in the database. so, my question is, in a standard instance of sql server installed on a server if the server restarts the id's jump by 1000. you can configure the server to stop this happening. does this same behaviour apply to a hosted instance of a database on azure. so if Microsoft restart the server for whatever reason would the id's in the hosted database jump just like standard installation behaviour. if this is the case can anyone recommend how this behaviour is switched off. i.e. is a script applied or is there a way to do it directly from within the azure portal. thanks in advance.

Answer 1

Good day @stephen polin

Azure SQL database Indentity column auto increment jump 1000

This is perfectly normal

does this same behaviour apply to a hosted instance of a database on azure.

Yes. This is how SQL Server works with identity properties in all versions/editions of SQL Server

This is how SQL Server designed for a better performance. identity does not and should not guaranty that you will not have gaps.

Note: You can configure IDENTITY_CACHE to be OFF, but this is HIGHLY NOT RECOMMENDED!

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15

Answer 2

The identity field jumping 1000 problem ONLY occurs if the SQL Server is not shutdown properly. This normally does not happen.

Answer 3

thanks for your response. what are the reasons this is not recommended?