SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the next identity value which created gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.

The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is missing identity and then we will move on to the solution.

SQL SERVER - Reseed Identity of Table - Table Missing Identity Values - Gap in Identity Column identitymiss

Developers can easily deter the above issue by avoiding gap in sequence of identity column through two additional SQL Tricks of reseeding identity.

We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.

USE AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM
TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT
@MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

SQL SERVER - Reseed Identity of Table - Table Missing Identity Values - Gap in Identity Column identitymiss1

I hope is solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Identity, SQL Index, SQL Scripts, SQL Server DBCC, SQL System Table
Previous Post
SQL SERVER – IntelliSense Does Not Work – Enable IntelliSense
Next Post
SQL SERVER – Very Powerful and Feature-Rich Backup, Zip and FTP Utility SQLBackupAndFTP

Related Posts

40 Comments. Leave new

  • I’m using the MS book “build a program now visual basic 2008” to learn sql. Unfortunately, the one database example uses the identity numbers fropm one table as the column of another table. My simple example reseeded one table when i fixed a few typos, with no way to renumber the identiy column. So, it seems the advise here is to never refer to an identity column in another table, which I will try hard to follow. It appears that one author at MS hasn’t learned this lesson yet.

    Reply
  • Can we use other thing rather then IDENTITY Column, and it can work if 100 Users hit the Table and Always get the Unique ID.

    If there is any thing please do update me …..

    [email address removed due to privacy issue]

    Reply
  • ZanyarFromIran
    March 3, 2010 5:24 am

    We should suppose that the ID column in this problem is not a PrimaryKey column. Because when we try to reorder a PrimaryKey column, we are in a incorrect direction. The main property of a PrimaryKey is that it should be unchanged all the time.

    Reply
  • Cropping identity columns’s is just plain fun:

    * it forces you to recheck that all tables that depend on that column are linked trough a foreign key constraint and are properly defined in diagrams

    * to finally solve the multiple cascade paths could lead to
    cycle issue trough writing custom update procedure/triggers

    *to rethink if certain decisions you made in the passed, based on the set of business constraints at that time, are still valid now

    *to make column names over the database less ambigious

    *hey my cropped identity fits in a smaller type (now that my relationships are all properly defined SSMS auto-adjusts the types in dependent tables)

    *finally the traces of importing data out of various databases, tables, the work of others … are starting to fade, aahh a fresh start. i feel so clean, finally i can delete that useless foreign key column that points to a non-exisiting database
    it starts to look as though i built it from scratch without the clutter of other co-developers
    jada jada (getting emotional)

    *BUT it remains utterly pointless none the less, pointless but fun!

    Reply
  • NOTE: (now that my relationships are all properly defined SSMS auto-adjusts the types in dependent tables) was surrounded by “dreaming” html tags

    Reply
  • great, thanks! solves my problem.

    Reply
  • I’ve ran into a similar problem.

    I created a DEV database, where IDs started at 1.
    I created a TEST database where IDs started at 10000
    and in PROD the seed was 20000.

    This was for a files table, which kept track of all uploaded files from an application.

    Initially that was ok, so that there was a buffer between the different servers, and it was easy to tell what’s where and why.

    But a day came when someone needed to copy prod data to Test. And then when testers began testing on the Test server, all the files in the 20000 range, started to be overwritten with the test files.

    I tried to go back into the DB and reseed it again to like 11000, but it didn’t work, and kept on inserting in the 20000 range.

    It would be great if there was a way to say ok, there are a bunch of skipped IDs, go and fill those first, and then skip over the existing ones and keep going.

    Reply
  • Good one

    Reply
  • håkan englund
    October 6, 2011 12:56 pm

    don’t delet rows. Mark them resting 0, or active 1 in a column then you dont get gaps

    Reply
  • I have two tables

    s_sl_header and s_sl_detail

    bothe tables have serial = serial , in the s_sl_header i have serial,shop, date,
    in s_sl_detail , serial, ticket (number)

    i want to find missing ticket number and should results are like this

    shop ,date,missing ticket
    6222 , 01-01-2012, 4432

    could you please help me to get the above results in sql2000.

    Thanks in advance for your help.

    Reply
  • hi Pinal,

    Really thanks to ur explanation.
    But in my case, I need to retrieve the top five rows from a table which has no gaps in its primary key, which is generated from a sequence.
    can u help me to achieve this output..

    thanks in advance,
    Ditz

    Reply
  • Hi Pinal,
    I need to explain you my situation and wanted to know, if you can help me in that : I really don’t know should I mention this issue in this area of blog or not..Please let me know, if I am on wrong path.

    We are using SQL Server 2008 R2. I am doing all my work through Managment Studio. We Managed Test DB. Due to some primary key issue we reseeded our Test DB (some tables) identity columns to +20000000.(Max Identity now became from 60000+20000000).. Ok now after reseed we take DB backup and restore it to some other DB. After restore we lost whatever we reseeded +20000000..

    I know there is no entry in that identity column from those number from 60000 to 20060000..and I wanted to know how should I prevent this problem happening. I don’t want to miss that identity from one Test DB to another Test DB.
    Please let me know, if you need additional information

    Thanks
    – Jojo

    Reply
  • Now this is simple for a table with fewer columns. what if the table has many columns?

    Reply
  • Thank u pinal ;-)

    Reply
  • Can anyone please tell how to fix this? Thank you.

    I got a weird result after testing the example given in the tutorial. The reseeding identity column worked well except that new records now on top, not next to the last record of the first batch like this:

    12 second
    11 second
    10 second
    9 second
    8 second
    1 first
    2 first
    3 first
    4 first
    5 first
    6 first
    7 first

    Reply
  • Ramamoorthi.S
    August 7, 2014 1:53 pm

    I think that best solution.. try if you it…

    DECLARE @INC AS INTEGER
    SET @INC=1
    WHILE (@INC @INC) –ID=> Identity column
    SET @INC = @INC+1
    END
    Note: before executing that you need to remove the identity insert to the column.

    Reply
  • hi,

    what if i am not using the identity column, i just based on the successful transaction, then follow by the max ranking then update the column ‘ranking’in the table.

    eg : t1 ranking 7
    t2 ranking 8
    t3 ranking 10
    the ranking value is i take the max ‘ranking- (8 + 1) then set the t3 ranking to 9, but somehow it jump to 10.

    how to avoid this?

    thanks.

    Reply
  • Hola, como se podría evitar esto? porque cada vez que reinicio el correlativo se saltea?
    [English translation by Google: Hello, as you might avoid this? because every time you restart the correlative is skipped?]

    Reply
  • Hi This is really helpful.Thank you so much for your post

    Reply

Leave a Reply