How to address a huge jump in identity number?

SQL Server Administration
1

Hi, I have just noticed a table of ours which has big jump in identity numbers. It only has 240k rows, with a standard INT identity column. From about row 140k, the identity number jumps a massive 1375554757 and increments from there for the rest of the rows. The table doesn't get many deletions or updates, mainly inserts.

I used a LAG function to compare adjacent IDs and pull out differences of over 1000, like this.

SELECT 
  r.PrevCodeId, r.CodeId, r.CodeId - r.PrevCodeId AS Diff
FROM (
  SELECT 
    c.CodeId, 
    LAG(c.CodeId, 1) OVER (ORDER BY c.CodeId) AS PrevCodeId
  FROM sv_360_Codes c
) AS r
WHERE r.CodeId - r.PrevCodeId > 1000
ORDER BY r.CodeId;

The result in question looks like this:

PrevCodeId   CodeId        Diff
189756       1375744513    1375554757

The jump may have happened when we migrated from our previous hosting provider to Azure, though I'm not sure why it would have caused it to reseed to such a huge number.

What is the best advice currently about how to address this? There are 2 table that rely on that ID as a foreign key. I could feasably write some code to re-assign IDs after reseeding, but I'm wondering if there's an easier way, or some tools that can help? How do people usually address this issue?

Many thanks!

3

Hi harishgg1, I'm aware of IDENTITY_CACHE and jumps of 1000 IDs, but that is not the case here. As stated, the jump is 1375554757, out of nowhere, so it appears to be a different issue and I'm asking what could have caused it and what can be done to "fix" it.

4

hi

what could have caused it .. there can be 100 different reasons
whats your particular case .. you will have to debug ..

make a checklist .. and narrow down to cause

please see below links

just google search . see links .. all will give different ... find one works for your situation

5

Here's how you'd fix it:

  1. Reset the identity value on the table to the value before the big jump. Say the last value used was 1234567. Then you'd issue this command:
    DBCC CHECKIDENT(sv_360_Codes, RESEED, 1234567)

  2. Copy all rows after the big jump back to the table, to get a new identity value assigned:
    INSERT INTO dbo.sv_360_Codes SELECT <all_non_identity_columns> FROM dbo.sv_360_Codes WHERE $IDENTITY > 1234567

  3. Delete the rows you just copied:
    DELETE FROM dbo.sv_360_Codes WHERE $IDENTITY >= 1375700000

1 Like