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:
- Query for the max id for the day before.
SELECT MAX(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-18') = 0
- Query the min id for the next day.
SELECT MIN(ID) FROM <table name> WHERE DATEDIFF(d, <date column>, '2013-03-20') = 0
- 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″]
- 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.
Nice article,
Before a few days ago, I was working on production report and found that some number is missing. This sequence and missing number are very important.
Immediately one requirement come to my desk is, please find a list of all missing number for investigation purpose.
I have also prepared my solution as one blog post.
Please refer this solution here:
http://www.dbrnd.com/2015/06/query-to-find-missing-number-id-sql-server/