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)