CODEWITHSUNDEEP
azure-sql-database
user1943020
user1943020

Reputation:

Azure SQL Server identity key values jumping up by 1,000

I am using the SQL Server 2012 that MSOFT provide for Azure. My identity columns have a habit of jumping up by 1,000 sometimes even though they are "INT IDENTITY (1, 1) NOT NULL" in my table.

Is there anything I can do to stop this happening. What about if I remove all rows from a table? Seems like even after I delete every row then when I add a new row it starts off with an ID that's more than 1,000.

Upvotes: 5

Views: 2781

Answers (1)

acarlon
acarlon

Reputation: 17272

Refer to this post and this answer. Basically, this is by design and the argument as to why this is not too much of an issue is that azure database limits will be exceeded before hitting the identity limit. There is also the option of using a bigint.

There is no explanation as to why the jump in seed is done when the database is bounced, but I am guessing it has something to do with concurrency problems that might result in the same identity being used for two records at the boundary between shutting down and restarting (for some reason I can't think of).

Upvotes: 4

Related Questions