SQL SERVER – Find Missing Identity Values

SQL SERVER - Find Missing Identity Values Missing-Identity-800x502 I recently received a question during Comprehensive Database Performance Health Check about how to Find Missing Identity Values. Let us learn about that today.

Let us assume that we have a table with an identity column and some of the identity columns are missing. Here is an example of the table. Due to some reason, we want to find the missing identity columns that have been deleted from it. For example here is the expected output.

SQL SERVER - Find Missing Identity Values MissingIdentity

The question is how we can do that. Well, there is a very simple method to do this task. Here is the script which you can use to find missing identity values. There are many different methods to do this task but this task, I am finding to be most effective.

SELECT Seq
FROM (SELECT ROW_NUMBER() OVER (ORDER BY c1.column_id) Seq
FROM sys.columns c1
CROSS JOIN sys.columns c2) SequenceTable
LEFT JOIN BigTable ON BigTable.ID = SequenceTable.Seq
WHERE BigTable.ID IS NULL AND
Seq < (SELECT MAX(ID) FROM BigTable)
GO

The script above will give us the necessary output. Let me know what are your thoughts about this quick blog post. Would you like me to convert this blog post to a video? Do let me know and I will be happy to do so. Additionally, I suggest you subscribe to my YouTube Channel where I share many learnings.

If you have any questions, you can always reach out to me on Twitter.

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

,
Previous Post
SQL SERVER – Using Stored Procedure in SELECT Statement
Next Post
SQL SERVER – Sample Script for Compressed and Uncompressed Backup

Related Posts

2 Comments. Leave new

  • Ken Sturgeon
    March 3, 2022 8:20 pm

    I always look forward to your posts and thank you very much for your desire to share knowledge. I’ve had the need to identify gaps and offer the script I developed.

    ;WITH cteRange
    AS (
    SELECT
    (SELECT ISNULL(MAX(ID) + 1, 1)
    FROM BigTable
    WHERE ID < md.ID) AS [from],
    md.ID – 1 AS [to]
    FROM BigTable md
    WHERE md.ID != 1
    AND NOT EXISTS
    (SELECT 1
    FROM BigTable md2
    WHERE md2.ID = md.ID – 1)
    )
    SELECT [from], [to],([to] – [from]) + 1 [total missing]
    FROM cteRange
    ORDER BY [from];

    Reply
  • Sushil agarwal
    March 4, 2022 9:06 pm

    Sir, I was asked by .net application user in his windows form id was identity column in the master and child tables. But today he noticed an unexpected jump in next by 1200 numbers, they do not enter I’d how this gap was created ? Can you guide us sir

    Reply

Leave a Reply