Interview Questions and Answers
How to get missing identity values from IDENTITY column?
Option 1:
SELECT DISTINCT id +1
FROM mytable
WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);
Option 2:
;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from @TT)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0);
Option 3:
Post a Comment
0 Comments