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.

1 thought on “Find Missing Identity Numbers in MS SQL Server

Leave a Reply