Can I get the deleted identity in sql?

WITH MissingValues (missnumber, maxnumber)
AS
(
SELECT 1 AS missnumber, (select max(ID) from Customer)
UNION ALL SELECT missnumber + 1, maxnumber FROM MissingValues
WHERE missnumber < maxnumber
)

SELECT missnumber FROM MissingValues
LEFT OUTER JOIN Customer e on e.ID = MissingValues.missnumber
WHERE e.OID is NULL OPTION (MAXRECURSION 0);

Regards,
Jayakumar Selvakani

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

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

hi,
try this links it might be helpful to you
http://stackoverflow.com/questions/9671899/sql-server-deleted-identity-columns
http://forums.asp.net/t/1906272.aspx/1?Reseed+identity+column+if+a+record+is+deleted
http://www.mindfiresolutions.com/How-to-get-the-Deleted-Identity-Numbers-in-SQL-Server-2005-799.php

Mark as Answer if it helps you
Thanks&Regards
Aswini Aluri

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator