Missing Numbers in a IDENTITY COLUMN
I came across a post in a forum related to finding out range of missing numbers in an identity column. (or a numeric column!)
There were a couple of solutions suggested by members. such as
SELECT T.IdCol + 1 AS IdCol, ( SELECT MIN(J.IdCol) FROM MyTable J WHERE J.IdCol > T.IdCol ) - IdCol - 1 AS Items
FROM MyTable T
WHERE ( SELECT MIN(J.IdCol) FROM MyTable J WHERE J.IdCol > T.IdCol) - T.IdCol >= 2
ORDER BY IdCol
GO
SELECT q.LowerMissing, q.UpperMissing, (q.UpperMissing - q.LowerMissing) AS [Difference] FROM
( SELECT (IdCol + 1) LowerMissing, (SELECT MIN(IdCol ) FROM MyTable WHERE IdCol > A.IdCol ) - 1 UpperMissing
FROM MyTable A WHERE
( SELECT MIN(IdCol ) FROM MyTable WHERE IdCol > A.IdCol ) - IdCol >= 2
) q
ORDER BY [Difference] desc
I worked out one for myself.
SELECT * , q.UpperMissing - q.LowerMissing + 1 TotalMissing FROM
(
SELECT A.IdCol + 1 LowerMissing , (SELECT MIN( IdCol ) FROM TableName WHERE IdCol > A.IdCol ) - 1 UpperMissing
FROM TableName A
) Q
WHERE (q.UpperMissing - q.LowerMissing) >= 0
ORDER BY 2