SQL Column ID value jumps 10000 times

The ID column index jumps 10000 times.

For example:

From index :

• 5 goes to 10006

• and the continues 10007 , 10008 , 10009

• and then goes to 20003 , 20004 ….

How I could fix the ID values and put them in order again like before?

Also I have find something about a Reseed function, but I do not know what it is and how to use it?

Answer

I’m assuming you’re using an identity column:

ID INT NOT NULL IDENTITY(1,1)

There’s no guarantee that this will remain in sequence. It was annoying when it first became more obvious (it didn’t appear to happen in older versions of SQL Server but apparently could happen) but was also always by design. The skipping became very apparent when 2012 (?) was released. You’re supposed to use a SEQUENCE now I believe if maintaining a steady sequence is required – e.g. invoice numbers:

https://dba.stackexchange.com/questions/62151/what-could-cause-an-auto-increment-primary-key-to-skip-numbers

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

It may also appear to skip if you perform an INSERT and it fails but this will only skip 1 typically. This has always happened and is by design – you need to reseed you identity to overcome this. Something like:

DBCC CHECKIDENT ("dbo.MyTable", RESEED, 10)

Will make the next identity number 11 provided the other skipping doesn’t also occur.

EDIT: In relation to re-aligning your existing entries I’m no DB Expert but I did do this the other day on a table using a fairly rudimentary approach – but it’s only a small table – there’s probably a better way to do it:

BEGIN TRAN

--CREATE TEMP TABLE
DECLARE @Tooltip TABLE
(
    [TooltipId] INT NOT NULL,
    [TooltipKey] NVARCHAR(100) NOT NULL,
    [Name] NVARCHAR(255) NOT NULL
)

--INSERT EXISTING INTO TEMP TABLE
INSERT INTO @Tooltip (TooltipKey, Name )
SELECT TooltipKey, Name
FROM dbo.Tooltip
ORDER BY TooltipId


--CLEAR ACTUAL TABLE
TRUNCATE TABLE dbo.Tooltip

--RESET IDENTITY TO 1
DBCC CHECKIDENT ("dbo.Tooltip", RESEED, 1)

--REINSERT FROM TEMP TABLE INTO ACTUAL TABLE
INSERT INTO dbo.Tooltip (TooltipKey, Name )
SELECT TooltipKey, Name
FROM @Tooltip
ORDER BY TooltipId

--TEST OUTPUT
SELECT * FROM dbo.Tooltip

--DO THIS FOR TESTING
ROLLBACK TRAN

--DO THIS WHEN YOU'RE CERTAIN YOU WANT TO PERFORM THE ACTION
--COMMIT TRAN

Bearing in mind that that if you have foreign keys or other references the truncate won’t work and you’ll have to do something more complex.Particularly if you have foreign keys referencing your existing incorrect IDs