I happened to be doing a little research on different methods for finding "gaps" and ran across this article. Here's a much faster way to find gaps... and, it doesn't use Temp Tables, Table Variables, or UDF's... No, there's nothing missing. This is the "find gap" code in it's entirety. 😛

SELECT GapStart = (SELECT ISNULL(MAX(b.ID),0)+1

FROM yourtable b

WHERE b.ID < a.ID),

GapEnd = ID - 1

FROM yourtable a

WHERE a.ID - 1 NOT IN (SELECT ID FROM yourtable)

AND a.ID - 1 > 0

My poor "old" computer is a 1.8GHz single cpu P5 and was built in 2002. This method is so fast that even when I return the gaps to the screen in the Grid mode, I still get at least 7 times the performance as follows...

Upper bound Author's Method This Method Performance Ratio

----------- --------------- ----------- -----------------

1,000,000 89 11 8.1:1

2,000,000 188 19 9.9:1

3,000,000 354 33 10.7:1

4,000,000 490 45 10.8:1

5,000,000 648 54 12.0:1

6,000,000 799 57 14:0:1

Of course, all times assume that you have a decent index, preferably a clustered one, on the ID column.

I agree with Mark's statement above, though... it's normally a "Bozo-no-no" to even think about reusing ID's especially if they're of the IDENTITY flavor.

Peet,

If your dates are "whole" dates, this method will also work for your date problem. Just make sure you have an index on the date column.