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.

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

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 (http://blog.SQLAuthority.com)

About these ads

40 thoughts on “SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

  1. Hi Pinal,
    I’m wondering if we have records like 1,2,3,5,6,8,9… then what? So here is the revised version of the above SQL statements which will insert into middle and at bottom.

    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 records from middle and bottom both*/
    DELETE
    FROM TableID
    WHERE ID IN (4,7,11,12)
    Go
    /* Check the records in table */
    SELECT *
    FROM TableID
    GO
    /*
    Now check the missing identity values
    To Do so we will create a memory table with a sequential number,
    from 1 to the ident_current value and join that to the live table.
    */
    Declare @rows Int
    Declare @numberOfRowsToInsert Int

    declare @seq table (
    seq int not null primary key
    )
    /* Get current Max Value and reseed table */
    SELECT @rows = MAX(ID) FROM TableID
    DBCC CHECKIDENT(‘TableID’, RESEED, @rows)
    set @rows = (select Ident_Current(‘TableID’)) + 5
    /* Insert values into Memory table */
    declare @i int
    set @i = 1
    while @i <= @rows
    begin
    insert @seq values( @i )
    set @i = @i + 1
    end
    /* Now set the Identity Insert On, so that we can insert any number */
    Set Identity_Insert TableID On

    INSERT INTO TableID(Id,col) Select seq,’Third’
    From @seq left outer join TableID T on seq = T.ID
    Where T.ID Is Null

    Set Identity_Insert TableID OFF
    /* Check the records in table */
    SELECT *
    FROM TableID
    GO
    /* Clean Database */
    DROP TABLE TableID
    GO

    -Vinit

  2. Why do people put so much meaning in the order of number, if it is an identity, it should just be unqiue, doesn’t matter in the order.

    if the order matters, generate it at run time!

    • How About if your customer doesn’t wan gaps in the client ids ?
      How about if you develop a software for a customer in a country where the law requires that there must be no gaps between order numbers ?

  3. ID column should have no business meaning so it doesn’t matter if it skips 1, 2 or a 1000 values. It just needs to uniquely identify a row (or entity).

  4. I agree with Anthony. As long as the values are unique, it should not matter what they are. If a solution relies on an uninterrupted sequence of identity values, then the solution is poorly designed.

    IMHO, any solution that uses integers as identity values should also be able to use GUIDs without any loss of basic functionality. If you can’t do that, you need to rethink what you’re doing.

  5. Kind of funny how we make assumptions on the design and not fully understanding why the junior developer did what they did.

    Without actually knowing what they were doing, kind of hard to say it was a design flaw, I think the whole point of the article was how to reindex identity column so that they are in sequence.

    Now if had been something I worked, then I would not care what is in the id column, I would have a second column that would be seen by the user.

    i.e. patient number, product sku, the id column would be something that could use to build a relationship lookup or nothing at all. Again depending on the design.

    Nice article, but I am sure that the author pointed out how to get around it and then probably asked the developer why they were doing that and point him/her in the right direction. :)

  6. People that need to do this are not understanding the right way to do this. You don’t need consecutive IDs, you only need UNIQUE IDs.

    IF you need consecutive IDs, create a VIEW that simply ORDERS the table with consecutive IDs.

    Trust me, you don’t need consecutive IDs in the TABLE.

  7. I fully agree with IrishManInUSA the basic purpose of id column is defeated if we rearrange the sequence of id. The Id is unique identity given to a row its not a serial no.
    (though in the article this is meant to be )

  8. hi Vinit,

    use the updated trigger on the table and get the result in sequence.

    like 1,2,3,4,5,6,7
    no 1,2,3,5,7,10

  9. Hi All,

    In the above example, the identity missing was found by manually. Is there any way to find the same using SQL statement ?

    ie, How we can find the list of tables whose identity was missed(not is sequential order) within the entire database?

  10. We are having an issue with the identify column of one of our normalized tables. The primary key (identity) of TableA is referenced by TableB and TableC.

    When we went to move this table from one database to another, the identity column was reseeded. This of course caused havoc with our data since records were being paired incorrectly.

    Is there a way around this?

  11. Some of the critics here are forgetting about inherited code and data (not OO inheritence, hand-me-down code).

    I just inherited a DB table that contains an ID column and an order number column that was not originally an Identity column. So, all records are unique on the ID PK column, but *should* also be unique on the order number as well…they are NOT.

    Some records have a NULL order number and some order numbers are duplicated. The difficulty is in making the decisions of how to rectify these issues:

    Can we delete the data if it is older? That is simple.

    If not, how to make this an Identity column and still retain the existing order numbers for historical purposes? SQL Server 2005 doesn’t let you make an existing column IDENTITY: “You cannot modify an existing table column to add the IDENTITY property.”

    How to find gaps (un-used numbers) around the same date range of the records with NULL or duplicate order numbers to try to use those to fix the bad records…we could simply use the next highest order number, but the users intuitively know the approximate date of the orders based on the order number and this would throw them off.

    If anyone has suggestions for these real-world issues, I’d be interested in hearing them.

  12. My humble experience of past few years (as a Sr.System Analyst & DB Architect) support the comments by Mr.IrishManInUSA also I support Mr.Ed McInerney.
    I believe Mr. Pinal was just conveying about such a method.
    The application of this thing may vary depends on situation. Anyway it’s very useful for a table with a dedicated column for customizable sort order, rather than the concept of primary key resorting.

  13. One reason I can think of when this may be needed.

    Lets say we have a lookup table where identity field was tinyint rather than int. Lets say someone entered 250 values that are now not required.

    Before now we delete those values and want to insert 10 new lookup values. But I will not be able to do so as I would have run out of numbers. Resetting the identity seed will allow me to enter my values.

    This is just one example of where resetting identity seed may be helpful….. There may be other ones…

  14. hi sir,

    i am not a regular reader but i have used ur tips and tricks for my knowledge.I am some confused about identity , that when i have 10 records in that i have deleted only one record that is 7 th record. then my identity no 7 will be missing for ever and even my identities are not in order .so will u let me know me how to reorder them .

    Thank You,

  15. 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.

  16. 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]

  17. 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.

  18. 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!

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

  20. 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.

  21. Pingback: SQL SERVER – Understanding Identity Beyond its Every Increasing Nature – Quiz – Puzzle – 3 of 31 « SQL Server Journey with SQL Authority

  22. 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.

  23. 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

  24. Pingback: SQL SERVER – List All Missing Identity Values of Table in Database « SQL Server Journey with SQL Authority

  25. 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

  26. Pingback: SQL SERVER – Weekly Series – Memory Lane – #023 | SQL Server Journey with SQL Authority

  27. 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

  28. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s