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.
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)
2 Comments. Leave new
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];
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