Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to find missing identity values

User-Defined Functions

How to find missing identity values

by  SQLWilts  Posted    (Edited  )
We all have found a time when an identity field, for whatever reason, is the answer to a problem. I hit the problem where I had to perform an identity insert into a table with an ID column on it, and then find the gaps after the table had been populated.
To do this, I will use a table variable and the SQL function Ident_Current.

Create and populate the table first
Code: 
Create Table dbo.Test (
                  TestID       Int Identity (1,1)
                  ,ProductCode  Int
                      )

Set Identity_Insert Test On

Insert Test (TestID, ProductCode) Select 1, 123
Insert Test (TestID, ProductCode) Select 3, 1234
Insert Test (TestID, ProductCode) Select 4, 4567
Insert Test (TestID, ProductCode) Select 6, 789

Set Identity_Insert Test Off

And now to find the missing identity columns.
Code: 
set nocount on


Declare @rows Int

declare @seq table (
   seq int not null primary key
)

set @Rows = (select Ident_Current('Test'))

declare @i int
set @i = 1
while @i <= @Rows
begin
   insert @seq values( @i )
   set @i = @i + 1

end

Select seq
From @seq left outer join Test T on seq = T.TestID
Where T.TestID Is Null

I use the Ident_Current function to find the last identity inserted on a table, and set a counter to this. I then create a memory table with a sequential number, from 1 to the ident_current value and join that to the live table.

Obviously, this will not be brilliant if you have millions and millions of rows, but it works well on smaller tables
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top