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)

, , , ,
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

  • 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

    Reply
  • Too much in a number
    April 1, 2009 2:45 pm

    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!

    Reply
  • Anthony Grace
    April 2, 2009 10:32 am

    If he is relying on the uninterrupted order of the keys, then that is a design flaw.

    Reply
    • 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 ?

      Reply
  • 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).

    Reply
  • 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.

    Reply
  • IrishManInUSA
    April 3, 2009 5:21 am

    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. :)

    Reply
  • 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.

    Reply
  • anand srivastava
    April 24, 2009 4:49 pm

    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 )

    Reply
  • 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

    Reply
  • IrishManInUSA is 100% right

    Reply
  • Dear Syyed,

    Can you kindly elaborate your solution about triggers

    Reply
  • 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?

    Reply
  • 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?

    Reply
  • 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.

    Reply
  • Hi pinal,

    thank you

    thank you very much

    Thanks & regrads

    Harishkumar.M

    Reply
  • 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.

    Reply
  • It was very helpful to me.
    Thanks

    Reply
  • 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…

    Reply
  • 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,

    Reply
  • Hello Jeswin,

    Deleted identity value is not used. There are some workarounds to get identity of deleted records. One is described here:

    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

    but that is not recommended for large tables as it degrade the performance of insert statements.

    Regards,
    Pinal Dave

    Reply

Leave a Reply

Menu