FIND MISSING NUMBER
https://www.dbrnd.com/2015/06/query-to-find-missing-number-id-sql-server/
Query to Find Missing Number and ID in SQL Server
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.
In the above result you can find total 20 missing number. MissingID Column represents missing numbers.
Comments
Post a Comment