Find Missing Identity Numbers in MS SQL Server

While I was doing some support cases, I realized that there might be some rows missing from the database for a particular day. So, I took some time off to confirm that indeed there was missing rows from database before I submit a request to DBA to restore the database.

Example: I will try to retrieve missing rows for the date 2013-03-19.

I did the following query to confirm if there was indeed missing rows:

1. Query for the max id for the day before.

   SELECT MAX(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-18') = 0

2. Query the min id for the next day.

   SELECT MIN(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-20') = 0

3. Now, I do a comparison to check for missing rows.

   WITH MissingNo (missnum, minid, maxid)
   AS
   (
    SELECT <insert 1st query result> AS missnum, <insert 1st query result> , <insert the 2nd query result>
    UNION ALL
    SELECT missnum + 1, minid, maxid FROM MissingNo
    WHERE missnum < maxid AND missnum >= minid
   )
   SELECT missnum
   FROM MissingNo
   LEFT OUTER JOIN <table name> ON <table name>.[ID] = MissingNo.missnum
   WHERE <table name>.[ID] IS NULL
   OPTION (MAXRECURSION 0);

   [adrotate banner=”1″]

4. There you go, you missing row id should appear.

Everything combined:

WITH MissingNo (missnum, minid, maxid)
AS
(
 SELECT (SELECT MAX(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-18') = 0) AS missnum, 
 SELECT MAX(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-18') = 0 , 
 SELECT MIN(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-20') = 0
 UNION ALL
 SELECT missnum + 1, minid, maxid FROM MissingNo
 WHERE missnum < maxid AND missnum >= minid
)
SELECT missnum
FROM MissingNo
LEFT OUTER JOIN <table name> ON <table name>.[ID] = MissingNo.missnum
WHERE <table name>.[ID] IS NULL
OPTION (MAXRECURSION 0);

If you have a better idea, do not hesitate to post in the comments section! 🙂

PS: This saved my time from asking DBA to do restoration as there’s actually no missing rows.

Notes: This method will not work if you set your identity specification (identity increment and identity seed) to anything higher than 1.