Finding gaps in an identity column (or any integer based column for that matter) using MS SQL

Few days ago one of my friend ask me to do a query to find gaps in an identity column in MS SQL. This is not that tough i thought and started to work on it. But i was given a boundary, no Stored Procedure can be introduced. Need to write it in query and if possible then in a single query! I found some solution but those were for MS SQL 2005 and got a comment "With Sql Server 2000, it would be extremely difficult and would require some iterative techniques."

But now i have a solution for that and want to share with you. Here it is with complete query for Table Creation, Data insertion and Selecting query.

Table query :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StudentInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[StudentInfo]GOCREATE TABLE [dbo].[StudentInfo] ( [SlNo] [int] NULL , [Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Roll] [int] NULL ) ON [PRIMARY]
GO

Inserting Data:

Insert Into StudentInfo Values(1,'Zakirul', 1)
Insert Into StudentInfo Values(2,'Mamun', 2)
Insert Into StudentInfo Values(3,'Robin', 3)
Insert Into StudentInfo Values(5,'Fakhrul', 4)
Insert Into StudentInfo Values(7,'Shamol', 5)

Selection Query:

SELECT DISTINCT (a.SlNo + 1) gapId Into #tmp FROM StudentInfo a
WHERE NOT EXISTS
(SELECT * FROM StudentInfo b WHERE b.SlNo = a.SlNo + 1)
AND a.SlNo < (SELECT MAX(SlNo) FROM StudentInfo)

UNION ALL

SELECT a.SlNo + 1 FROM StudentInfo a
WHERE NOT EXISTS
(SELECT * FROM StudentInfo b WHERE b.SlNo = a.SlNo + 1)
AND a.SlNo > (SELECT MAX(SlNo) FROM StudentInfo)



SELECT gapId AS BitPosition FROM #tmp ORDER BY gapId

It is working nicely for my domain, hope work with yours. Feel free to ask me any question regarding this.

Thanks,
-Zaq