question

Andomar avatar image
Andomar asked

Modify sequence behind identity

As detailed in this [blog post][1], in SQL Server 2012 it is possible for an identity column to skip thousands of values. The suggested cause is that in 2012, identity is really a sequence with the `cache` property set. So when SQL Server restarts or fails over, you loose all the cached identity numbers. Is there a way to get to the sequence behind an identity column, and modify its properties to include `no cache` ? [1]: http://www.sqlserver-training.com/sequence-breaks-gap-in-numbers-after-restart-sql-server-gap-between-numbers-after-restarting-server/-
sql-server-2012identitysequencecache
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered
This is reported as a bug in SQL Server 2012. I was the first to spot it back in April. [It's been reported on Connect][1]. Go to the Connect page. There are several workarounds you can apply in order to deal with this better. [1]: https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andomar avatar image Andomar commented ·
Interesting link and gratz for spotting the issue first. But my question is whether you can modify the sequence behind an identity column?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
You can reseed it and set the values there. Other than that, the workarounds on that page have other suggestions. But those are your only options. Reseed is here: http://msdn.microsoft.com/en-us/library/aa258817(v=SQL.80).aspx
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
I don't read that blog to say that the column is an IDENTITY type. I have never known an IDENTITY column leave gaps in its values after a service restart.
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andomar avatar image Andomar commented ·
You're right that the blog doesn't mention identity. But identity is also affected, see http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value#tabs
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.