SQL SERVER – List All Missing Identity Values of Table in Database

The best part of any blog is when readers ask each other questions. Better still, is when a reader takes the time to provide a detailed response.

A few days ago, one of my readers, Yasmin, asked a very interesting question:

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

A big thank you to SQL Server Expert, Imran Mohammed, for his excellent response to this question. He also provided an extremely impressive script, which is well described and contains inline comments.

This script is well worth keeping in your archive for future reference.

Click to Download Scripts

If you and use the script, please comment here and let me know about your experience.

If you know any other way that this can be accomplished or have a different solution, please post your comment here, or send me an email and I will publish it with due credit.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

42 thoughts on “SQL SERVER – List All Missing Identity Values of Table in Database

  1. Very interesting script.

    I had recently a discussion with Pinal (while travelling to delhi for PASS chapter launch) about cases where missing identity values can create a problem. After a very long discussion we could not really identify any such case.

    IDENTITY values are not expected to be sequential and there are all chances of having missing identity values, the most common cause is transaction rollbacks.

    It will be really interesting if any of the readers here can come up with some use cases where missing identity values create problems. I have seen a lot of people talking about missing identity values and that indicate that there may be some environments where IDENTITY columns are used in a certain manner that they should always be sequential. I am looking forward to know about a few such cases.

  2. I would love to know what cases have been identified where a missing identity value is causing issues. I really can’t think of a case that this would happen. The identity column is basically just a random number – even though they come sequentially. Is this a case of someone building logic around the next number to be used should be 1 more than the last? I just can’t think how there would be a dependency on a random number being generated by the DB engine and creating problems in any other layer – other than a developer making an assumption that the next record inserted will have an identity that is 1 more than the last inserted record. And if this is the case – then there are flaws in the logic of the developer relying on the engine to add an arbitrary number to a record. Identity is not a record number – and should not be treated as such.

  3. i also don’t face any problem yet where missing identity values
    create some problem with anything, but yes this script is quite interesting, i never thought of this before. This one is really good.. so now hunt for the issue when missing identity create problems let’s see if we find something like this..

    Thanks for script..

  4. Jacob, thank you for pointing this. I think the same way so I was quite curious for what this script can be use in practice (for fun and sql learning is great). Identity is not “row number” column as a lot of beginners like to think ;)

    But maybe someone will give us some practice example for identity column “recovery” needs.

    Greetz

  5. Its good script. but i am curious to know why Yasmin want this kind of script unless she has any business reason!!!

    I am sure its a million dollars question :D

    Hey pinal sir ji, Why don’t you open have poll on this!!! it would be interesting to see, if any one in this world has any good reason behind this!!!

    • Hello All,

      I think it totally make sense to ask this question. I will write down new post combining all the questions and comments from this blog and ask it again to see what do I get email.

      I will also send email to Yasmin for the same.

      Kind Regards,
      Pinal

  6. Hi Vijaya Kadiyala,

    Yes, we have to know reson to identity identity columns in hole DB.

    For example you have some transaction tables with foreignkeys but does not have primary key. Those table performances will be slow at the time of reading the data.

    So, you want to find out whose tables does not have either identity or primary keys. At this time this script will be usefull.

    Thank you,
    Gangadhar.

  7. Gangadhar Naidu,

    Well, the question really is why we should bother about missing identity values. What if the value in my table is 1, 2, 3, 5, 6 etc where “4” is missing from the sequence.

    Several people opined in this thread that identity values are expected to be ‘non-sequential’. So what is the importance of knowing whether a table has missing identity values or not?

    regards
    Jacob

  8. i think till then developers don’t make assumptions like that
    ” Next order must be +1 then previous one ” or “customerid is always in sequence” till then these assumptions are not made by developer. Well still looking for some other problems arouses by Missing or Non-Sequential Identity value in identity columns. :)

  9. Hello All,

    Thanks Pinal for keeping this script to download on your blog.

    Thanks for those who actually saw this script and gave missed comments.

    Now response for all above questions.

    I do not want to show off, But to be honest, When I see a question posted in this blog, I don’t give a second thought, Why, What, When, Where …. NO, But I straight away start, working on the question.

    Few Questions that I find very good and if I feel I developed a good script for such a question, then I sent that script to Pinal requesting him to publish.

    For this specific question, I did not think why user needed this info, I found this question challenging and I gave a solution.

    There could be many reasons to find out why we need this info, If you use Identity property as your most unique column and Transaction Identifier, then definitely you would want to know why few transaction did not completely, Is there any specific fashion these transaction fails (Can be found out looking at missing values of identity)….. Could be helpful to debug.

    ~ IM.

    • Imran,

      Very nice comment Imran.

      As you said, this can be for sure helpful to debug. I have personally once seen issue where someone was relying on identity to to assign them seats in events. In fact, it should be depending on Count(*).

      One more example, I remember when one of the ID was missing and company owner’s superstitious nature was not happy with it.

      Well, above two reasons does not quality but debugging for sure help out.

      Again, writing script has nothing to do with expertise. We want to train our brain and that is why we try to solve puzzles.

      Great work Imran! At present no one else has attempted to solve this puzzle.

      Kind Regards,
      Pinal

  10. Hi Imran,
    You are right and most of us do the same on the forums. When I see a post, usually I provide a solution, without asking ‘why’ the OP actually needed it. And I think that is a decent way of supporting the community.

    This discussion came not as a criticism to the ‘excellent’ script provided for download. But it was more focusing on a ‘learning’ excercise. IDENTITIES are discussed over a million times in SQL Server blogs. Though identities are looked as a trivial subject, it is not so. We once did a 1 hour Quiz session on IDENTITIES.

    The discussion I started was with the intention to see if there are any REAL-LIFE example where a missing identity sequence can really create a problem.

    regards
    Jacob

  11. Hi,

    I couldn’t find any reason why we need to find out missing identity values. I have gone thru various forums but no luck.

    Every SQL expert says Why do you care about missing values :D…

  12. Yea also agreed from what Jacob Sir says usually we just start solving the things without asking why people need it, but its good to find a real world example where we really use it and do tell other peoples about it…

  13. i know the details about SQL from this blog. the above question is very useful to me. your blog is very useful to know the answer to that question. thank you so much. In SQl lot of unknown details are there. so i watch this site regularly to know lot.

  14. i know the details about SQL from this blog. the above question is very useful to me. your blog is very useful to know the answer to that question. thank you so much. In SQl lot of unknown details are there. so i watch this site regularly to know lot. SQL server is very useful database when compared with other

  15. Pingback: SQL SERVER – Discussion – Effect of Missing Identity on System – Real World Scenario Journey to SQL Authority with Pinal Dave

  16. Hmmm… it’s overkill.

    SELECT ID+1 FROM Tablename T1
    WHERE NOT EXISTS
    ( SELECT ID FROM Tablename T2 WHERE T2.ID = T1.ID+1 )

    KISS, isn’t it?;)

    // You may add additional check to exclude last value (it equals identcurrent for Tablename)

  17. As for one example where a missing identity column definition can cause problems…if you have a script doing an INSERT INTO you MUST have all of the columns defined in the insert statement or an error will result–EXCEPT if one of the columns is an IDENTITY column. In fact, if this script is inside a stored procedure the sp will not even compile, telling you that a column is missing. Of course, you can screw around in your script generating the next number for an insert for the column and make sure it’s in the list of columns of the INSERT INTO…but setting the column property correctly is sure a lot easier and more efficient.

    Now I had a different problem that was solved by this script. I was trying to rebuild an entire database manually. Don’t ask why, I just had to do it. I was able to script out all of the tables, procedures, keys, functions, etc., and import all of the data. But I needed to know what tables in the source db had identity columns. This script gave me the answer. Excellent work!

  18. Pingback: SQL SERVER – Find Gaps in The Sequence Journey to SQL Authority with Pinal Dave

  19. This was just what I was looking for…though I wanted the details for just 1 table..and I tweeked it accordingly…but the logic was ready and available
    This was of great help…

  20. While we are migrating master data from one server to another identity column place major role. We might need the same values as IDs to be uploaded to production server from devserver. If production server table misses some identity values that are there in dev server then we may need to identify missing idenity columns

  21. SET NOCOUNT ON

    –Create an table in which we can add identity and Values.
    IF OBJECT_ID(‘tblNumbers’) IS NOT NULL
    BEGIN
    DROP TABLE tblNumbers
    END

    CREATE TABLE tblNumbers
    (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    VALUE VARCHAR(20)

    )

    –Create an table in which we can add numbers.
    IF OBJECT_ID(‘tblAllNumbers’) IS NOT NULL
    BEGIN
    DROP TABLE tblAllNumbers
    END

    CREATE TABLE tblAllNumbers
    (
    NUMBER INT PRIMARY KEY

    )
    –Create an Number table upto 15,00,000
    INSERT INTO tblAllNumbers

    SELECT TOP 1500000 ROW_NUMBER() OVER(ORDER BY SC.ID) FROM sys.sysobjects SC , Sys.sysobjects SC1

    DECLARE @int INT,@MaxRowXount INT

    –Insert Row data into tblNumbers
    SELECT @int = 1

    WHILE @int 0

    SELECT @MaxRowXount = MAX(ID) FROM tblNumbers

    –Now Check for Missing Identity.

    SELECT TA.NUMBER FROM tblAllNumbers TA LEFT JOIN tblNumbers TN

    ON
    TA.NUMBER = TN.ID

    WHERE
    TA.NUMBER <= @MaxRowXount AND
    TN.ID IS NULL
    ORDER BY
    TA.NUMBER

  22. you can do this with Recursive CTE also,

    Declare @t table (ColId varchar (20))
    insert into @t
    Select ‘EC0001′ union all
    Select ‘EC0012′

    ;With CTE as
    (Select CAST(STUFF(MAX(ColId), 1,2,”) AS INT) colid from @t
    union all
    Select colid-1 colid from CTE where (colid-1)>=1 )
    Select ‘EC’+ replicate(‘0′,6-len(min(colid)))+cast(min(colid) as varchar) from CTE where
    colid not in (Select stuff(colid,1,2,null) from @t)

  23. DECLARE @ID INT
    DECLARE @MaxID INT
    DECLARE @MissingCarTypeIDs TABLE ( [ID] INT )

    SELECT @MaxID = [ID] FROM Missingids

    SET @ID = 1
    WHILE @ID <= @MaxID
    BEGIN
    IF NOT EXISTS (SELECT 'X' FROM Missingids
    WHERE [ID] = @ID)
    INSERT INTO @MissingCarTypeIDs ( [ID] )
    VALUES ( @ID )

    SET @ID = @ID + 1
    END

    SELECT * FROM @MissingCarTypeIDs

  24. SELECT repeating_payment_id+1
    FROM pmnt_repeating_payment PRP1
    WHERE NOT EXISTS
    ( SELECT repeating_payment_id FROM pmnt_repeating_payment PRP2 WHERE PRP2.repeating_payment_id = PRP1.repeating_payment_id+1 )
    EXCEPT
    SELECT MAX(repeating_payment_id) + 1
    FROM pmnt_repeating_payment

  25. SELECT ID+1 FROM Tablename T1
    WHERE NOT EXISTS
    ( SELECT ID FROM Tablename T2 WHERE T2.ID = T1.ID+1 )
    EXCEPT
    SELECT MAX(ID)+1 FROM Tablename

  26. How to exclude data if Type in (‘A’, ‘B’) within same ID_Code and CreateDate columns. Here is value;

    ID_Code Type CreateDate
    1 A 7/1/2011
    1 B 7/5/2011
    1 C 7/2/2011
    2 A 7/12/2011
    2 B 7/12/2011
    2 D 7/14/2011
    3 A 7/12/2011

    Expected excluded data results should be this;

    2 A 7/12/2011
    2 B 7/12/2011
    2 D 7/14/2011

    • Try this

      select * from @t where ID_Code in
      (
      select ID_Code from @t
      where type in (‘A’,’B’)
      group by ID_Code
      having MIN(CreateDate)=MAX(CreateDate)
      and COUNT(ID_Code)=2
      )

  27. I have a Windows Application to manage Battery Sales.
    I have a db table ,named as “Table_Sales”, inwhich “InvoiceId” is set to the Primary key.
    The same field is set with “Identity Specification” to “Yes” inwhich “Identity Increment” as “1” and “Identity Seed” as “1”.
    But , the “InvoiceId” with value “482” is missing.
    There is no option to delete from the table in the application.
    Only we are inserting values to the table.
    What May be the reason to miss one of the row?
    How can i insert a new row with Invoice Id 482?

    Thanks,
    Prasobh Krishnan

  28. How can i insert a new row with Invoice Id 482?

    This is easy,you can turn off identity before insert,after insert successful turn on the indentity.

    Are you sure you are missing only 482 from the sequence ?Or there are some other missing number too.

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