Jeff Moden (10/2/2009)
--===== Find the "gap ranges"-- This assumes that gaps include any whole number greater than 0
SELECT GapStart = (SELECT ISNULL(MAX(b.MyID),0)+1
FROM #MyTest b
WHERE b.MyID < a.MyID),
GapEnd = MyID - 1
FROM #MyTest a
WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest)
It also doesn't need a mega-row tally table.
Why do I think the last line of that code should read WHERE a.MyID NOT IN (SELECT MyID + 1 FROM #MyTest) and a.MyID > 1
edit: I remember now why I hate html, at least with code sets other than unicode. It is something to do with "&" followed by "gt;" insteard of ">" and other similar inanities.
Tom