This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I will give you one full demo to find a missing number of identity column in SQL Server.
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.
Below is a full demo script to find the missing number in SQL Server.
First Create one sample table with data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/*Create one table to store Numbers*/ CREATE TABLE dbo.tbl_Numbers ( ID INTEGER ) GO /*Insert some test records. You can also see I have missed many number which we are going to find by query*/ INSERT INTO dbo.tbl_Numbers VALUES(1),(2),(3),(4),(6),(7),(9),(10) ,(11),(15),(16),(19),(22),(23),(24) ,(28),(32),(33),(34),(35),(38),(40) ,(41),(42),(43),(44),(45),(47),(49) ,(50) GO |
Below is step by step script to find this missing number.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
/*Step 1: Create one temp table and store all distinct Numbers*/ SELECT DISTINCT ID INTO #DistinctID FROM dbo.tbl_Numbers GO /*Step 2: Find NextID which has something missing before it. Store into one TempTable*/ SELECT (ROW_NUMBER()OVER (ORDER BY ID ASC)) AS TableCountID ,b.ID ,( SELECT Top 1 ID FROM #DistinctID a WITH (NOLOCK) WHERE a.ID > b.ID ) AS NextID INTO #NextID FROM #DistinctID AS b WITH (NOLOCK) WHERE ( SELECT Top 1 ID FROM #DistinctID a WITH (NOLOCK) WHERE a.ID > b.ID ) <> b.ID+1 ORDER BY b.ID /*Step 3: Create one temp table to store missing number details */ CREATE TABLE #MissingID ( TableCountID INTEGER ,ID INTEGER ,NextID INTEGER ,MissingID INTEGER ,TotalDiff INTEGER ) GO /*Step 4: Now start to find missing id using WHILE Loop. */ DECLARE @CountID INTEGER DECLARE @InnerLoopCountID INTEGER SET @CountID = 1 WHILE (@CountID > 0) BEGIN INSERT INTO #MissingID SELECT TableCountID ,ID ,NextID ,CASE WHEN TotalDiff = 1 THEN ID+TotalDiff END AS MissingID ,TotalDiff FROM ( SELECT TableCountID ,ID ,NextID ,(NextID-ID)-1 AS TotalDiff FROM #NextID ) AS T WHERE TableCountID = @CountID IF ((SELECT (NextID-ID)-1 FROM #NextID WHERE TableCountID = @CountID)>1) BEGIN SET @InnerLoopCountID = (SELECT (NextID-ID)-1 FROM #NextID WHERE TableCountID = @CountID) WHILE (@InnerLoopCountID > 0) BEGIN INSERT INTO #MissingID SELECT TableCountID ,ID ,NextID ,ID+@InnerLoopCountID AS MissingNumber ,TotalDiff FROM ( SELECT TableCountID ,ID ,NextID ,(NextID-ID)-1 AS TotalDiff FROM #NextID ) AS T WHERE TableCountID = @CountID SET @InnerLoopCountID = @InnerLoopCountID - 1 END END SET @CountID = @CountID+1 IF @CountID = (SELECT COUNT(1)+1 FROM #NextID) BEGIN RETURN END END GO /*Step 5: Now select missing data from #MissingID temp table. You can find full details on MissingID.*/ SELECT *FROM #MissingID WHERE MissingID IS NOT NULL ORDER BY MissingID GO |
Result of Step 5:
In the above result you can find total 20 missing number. MissingID Column represents missing numbers.
