Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Programming FAQ

User-Defined Functions

How to find missing identity values by SQLWilts
Posted: 28 Feb 07

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


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.


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
   insert @seq values( @i )
   set @i = @i + 1


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

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close