Finding gaps in column of sequential numbers

The script below should help you with this. The performance of this is relative to the number of rows within your employee table.

For the purpose of this example I will create a fake employees table. In real life you would replace this with your existing one.

 create table dbo.EmployeesExample ( EmpID char(4) PRIMARY KEY, FirstName varchar(50), LastName varchar(50) ) insert dbo.EmployeesExample VALUES('0001','Laura','Jones') insert dbo.EmployeesExample VALUES('0003','Stephanie','Smith') insert dbo.EmployeesExample VALUES('0017','William','Black') insert dbo.EmployeesExample VALUES('0020','Linda','White')

Now this is the code that finds the missing values.

 create table #MissingIDS ( EmpId char(4) PRIMARY KEY ) declare @maxid int, @currentid int --Find the current maximum employee id SELECT @maxid=max(cast(empid as int)) FROM dbo.EmployeesExample select @currentid=1 --Loop around sequentially populating the temporary table with every --possible employee id while @currentid<@maxid begin insert #MissingIDS select right('0000'+cast(@currentid as varchar(4)),4) select @currentid=@currentid+1 end --delete the existing employee ids from the temporary table --by joining to the Employees table delete #MissingIDS from #MissingIDS m inner join dbo.EmployeesExample e on m.empid=e.empid --And what you have left is the missing ids select * from #MissingIDS drop table #MissingIDS

Now this is the code that finds the missing values.

 create table #MissingIDS ( EmpId char(4) PRIMARY KEY ) declare @maxid int, @currentid int --Find the current maximum employee id SELECT @maxid=max(cast(empid as int)) FROM dbo.EmployeesExample select @currentid=1 --Loop around sequentially populating the temporary table with every --possible employee id while @currentid<@maxid begin if not exists(select empid from dbo.EmployeesExample where EmpID=right('0000'+cast(@currentid as varchar(4)),4)) insert #MissingIDS select right('0000'+cast(@currentid as varchar(4)),4) select @currentid=@currentid+1 end --And what you have left is the missing ids select * from #MissingIDS drop table #MissingIDS

For More Information

• Dozens more answers to tough SQL Server questions from Tony Bain are available here.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.