sql_lock (12/7/2012)
Try this
;WITH Missing (missnum, maxid)AS
(
SELECT
1 AS missnum, (select max(RegisterNo )
FROM
dbo.studentMaster)
UNION ALL
SELECT
missnum + 1, maxid
FROM
Missing
WHERE
missnum < maxid
)
SELECT missnum
FROM
Missing
LEFT OUTER JOIN
dbo.studentMaster tt on tt.RegisterNo = Missing.missnum
WHERE
tt.RegisterNo is NULL
OPTION (MAXRECURSION 0);
Take from
Oh, be careful now. There are two things wrong with such a thing. The first is that it's a Recursive CTE that counts and is frequently slower than using even a WHILE loop. See the following for more on that subject.
http://www.sqlservercentral.com/articles/T-SQL/74118/
The second thing is that if you have offset ranges with large gaps between the ranges, you could be calculating for a very, very, long time.
--Jeff Moden
Change is inevitable... Change for the better is not.