Missing Identity

Editor's Note: Share your SQL Server tips and tricks with other SQL Server Magazine readers. Email your ideas (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.

Here's a tip for people who are engrossed in finding missing identity values. SQL Server generates identity values serially when you turn on the IDENTITY property for a particular column of a table. You can lose the sequence of these values when rows are deleted from the table. To make recovering missing identity values faster, I developed the query in Listing 1, which shows you the mathematical difference between the IDENTITY column values if any part of the sequence is missing. To see how this code works, let's walk through an example. First, insert some additional values into the Region table in the Northwind database, as Table 1 shows. I've inserted four new rows. Next, use the following query to delete some of the rows from the table so that some identity values are missing:

DELETE FROM region WHERE regionid IN(3,6,7)

Now execute the query that Listing 1 shows. This query is especially helpful, for example, when you're working with a table that contains more than a million rows and is missing only one or two values from the columns. In such a case, rather than scrolling through all the table's rows, you can use Listing 1's query to automatically show the culprit rowset.

—Vishal Parkar
[email protected]