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.
-
that's a known 'feature'....Mitch Wheat– Mitch Wheat2013年12月27日 08:56:35 +00:00Commented Dec 27, 2013 at 8:56
-
Is there a way to "start again" with the ID number other than deleting the table. Do you know if they plan to remove that feature from the next SQL Server version?user1943020– user19430202013年12月27日 10:33:40 +00:00Commented Dec 27, 2013 at 10:33
-
Explain why it is a problem. An identity column is a surrogate key and the value should not be that important.Craig– Craig2013年12月28日 05:12:49 +00:00Commented Dec 28, 2013 at 5:12
1 Answer 1
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).