Hi,
I'm a latecomer to the party and didn't realize there had already been healthy debate, but here's what I came up with.
Looking through the previous submissions above, it's the same concept as Brendan's, but extended to support the full functionality of the original post.
For the original example (working on an unoptimized temp table) the time taken seems to be approx 0.25 secs/1000 records, so with 10000 records I'm getting something like 3 seconds run time. With the code below there seems to be no increase in time taken at all (around 0.25 secs for anything from 0 to 10000 records), but this is probably because for small datasets the main delay below is not due to the work itself, but rather the creation of the temp table.
I'd be interested to see if anyone can make something faster! (ignoring the fact that I could probably have made it a table variable...)
Thanks,
Tao
CREATE TABLE #StartsAndEnds (EventID Int, EventType NVarChar(20), IdentityTracker INT IDENTITY (1,1))
INSERT INTO #StartsAndEnds (EventID, EventType)
SELECT SeqNumber, Type
FROM (
SELECT Seq1.SeqNumber, 'Start' AS Type
FROM #SequenceTable Seq1
LEFT JOIN #SequenceTable Seq2 ON Seq2.SeqNumber = Seq1.SeqNumber + 1
WHERE Seq2.SeqNumber Is Null
UNION ALL
SELECT Seq1.SeqNumber, 'End' AS Type
FROM #SequenceTable Seq1
LEFT JOIN #SequenceTable Seq2 ON Seq2.SeqNumber = Seq1.SeqNumber - 1
WHERE Seq2.SeqNumber Is Null
) AS PingPong
ORDER BY SeqNumber
SELECT IsNull(FL1.EventID, 0) AS StartGap,
FL2.EventID As EndGap,
IsNull(FL1.EventID, 0) + 1 AS FirstAvailable,
FL2.EventID - 1 AS LastAvailable,
FL2.EventID - IsNull(FL1.EventID, 0) - 1 As AvailableCount
FROM #StartsAndEnds FL2
LEFT JOIN #StartsAndEnds FL1 ON FL2.IdentityTracker = FL1.IdentityTracker + 1
WHERE FL2.IdentityTracker % 2 = 1
DROP TABLE #StartsAndEnds
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.