3

I recently had to move a database(sql server 2008) to a different server, and I have noticed that in one of the table, the value of identity column has started to get some unexpected values. its set as identity column with identity increment 1 and identity seed 1. After every 10 consecutive entry or so, it would start from another much higher number and increment by 1 for next 10 entries or so and then jump up to another higher number. I can't seem to figure out the issue.

Sorry for the layman language. I am not a DB person.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Joe
  • 255
  • 2
  • 6
  • 21
  • 1
    Why is this a problem for you? It's unexpected, but is it _bad_? – John Saunders Dec 21 '12 at 18:27
  • Well, I use the ID to identify that particular entry on a portal. Much like stack overflow for example uses the id http://stackoverflow.com/questions/13995265 – Joe Dec 21 '12 at 18:30
  • 2
    So why do you care what the value is, as long as the value is unique? – John Saunders Dec 21 '12 at 18:31
  • its not a big deal, as long as its unique, but I am just curious what might be causing this. – Joe Dec 21 '12 at 18:32
  • 3
    Remember that it's called an "identity" column, and not a "guaranteed sequential" column. – John Saunders Dec 21 '12 at 18:38
  • 1
    Have you restarted the service when this happens? http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity Also, are you sure rows aren't being deleted or inserts aren't being rolled back? – Aaron Bertrand Dec 21 '12 at 18:57
  • I havent restarted the service. – Joe Dec 21 '12 at 19:06

2 Answers2

4

This is likely not an issue with your identity key but an issue with a framework being used to insert data, or a SP. If you have a stored procedure that inserts data but then is ROLLBACK'd, the ID was reserved but the row is 'deleted'.

So two places to check: One on the frameworks you're using (NHibernate, or Entity Framework, etc?)... those frameworks might be inserting rows then deleting them. Second place to check is INSERT statements in SPROCs and other places you might expect a ROLLBACK.

See: SQL Identity (autonumber) is Incremented Even with a Transaction Rollback

Another issue is that you may just be examining data without sorting it? And when you ported the data you assumed it would be inserted or retrieved always in-ID-order. But because the new table is not 'indexed' the same way you won't necessarily see items in primary-key order. This is less likely if rows appear sequential most of the time with gaps, but worth mentioning.

Community
  • 1
  • 1
Richthofen
  • 2,076
  • 19
  • 39
  • I am using entity framework. Weird thing is, no code change has been made, i just had to move the DB to a new server and that's when I have start noticing the problem. I will take a look at the code. thanks – Joe Dec 21 '12 at 18:35
  • Its possible that if you moved to an UPGRADED database or different version, the Entity Framework code does database version checking at runtime and inserts/manages data differently. Check Entity Framework config options for inserting data. – Richthofen Dec 21 '12 at 18:36
  • Could also be a bug that manifests on failover / restart: http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity (and no, it does not affect just 2012 Availability Groups). It was also reported in this bug but closed as "by design" prematurely IMHO: http://connect.microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value – Aaron Bertrand Dec 21 '12 at 18:57
  • Okay, you might be right, the DB on the new server is 2012. And i saw the server got restarted this morning for windows update, so the service got restarted.hmmm – Joe Dec 21 '12 at 19:21
0

The following will shed some light on your issue.

  1. Create a table with an identity auto increment column
  2. Insert 10 rows of random data
  3. You will see that the ID values are 1,2,3,4,5,6,7,8,9,10
  4. delete from mytable where id=10
  5. insert another row into the table
  6. now you will see the ID values are 1,2,3,4,5,6,7,8,9,**11**
Alex Gordon
  • 57,446
  • 287
  • 670
  • 1,062
  • that's not the issue. I am not deleting any row. If there is a couple of numbers difference then i would consider in this direction. But it jumps to more than 1000 entries. – Joe Dec 21 '12 at 19:18