The above solution (Jayakumar's post) has limitation....
If the last ID of customer is deleted by user, then the above query won't show the last missing identity value...
So you can take current identity value by using
IDENT_CURRENT('TableName') instead of MAX(IdentitycolumnName)
DECLARE @maxIdVal int
SELECT @maxIdVal = IDENT_CURRENT('Customer') -- for example I took Customer table
;WITH MissingValues (missnumber, maxnumber)
AS
(
SELECT 1 AS missnumber, @maxIdVal
UNION ALL SELECT missnumber + 1, maxnumber FROM MissingValues
WHERE missnumber < maxnumber
)
SELECT missnumber FROM MissingValues
LEFT OUTER JOIN #CarType e on e.ID = MissingValues.missnumber
WHERE e.ID is NULL OPTION (MAXRECURSION 0);
Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif
Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator