Few years back I wrote a solution for the same problem here . Recently I read an article by Henning on the similar topic which was interesting and informative. After that, I was wondering why not just make use of a NUMBER table and a NOT IN to do this. I am providing the test script as-is and I haven't compared the performance yet between any other solutions out there :) --Lets set up a sample table and pump in few thousand records with gaps in it SET NOCOUNT ON; GO CREATE TABLE tblFindGaps ( ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED, [Name] VARCHAR(50) NULL ) GO INSERT INTO tblFindGaps (Name) VALUES ('a') GO 10000 -- Delete some rows to create gaps in the identity column DELETE tblFindGaps WHERE ID IN (23, 64, 3392, 44) DELETE tblFindGaps WHERE ID BETWEEN 5000 AND 6000 GO Solution: Lets create a table to hold numbers from 1 till the MAX available number already present in tblFindGaps table. -- Number t...
I write about things which I am passionate about.