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...)

I copied your code above and did a find/replace on #SequenceTable and SeqNumber swapping these for my table name and sequence field respectively, then ran it. It was certainly quick, very impressive, 16 seconds to produce 142874 identified breaks in the sequence from a table containing 3,827,625 rows.

First few rows of output are as below

StartGap

EndGap

FirstAvailable

LastAvailable

AvailableCount

0

NULL

1

NULL

NULL

0

NULL

1

NULL

NULL

0

NULL

1

NULL

NULL

0

NULL

1

NULL

NULL

0

NULL

1

NULL

NULL

0

NULL

1

NULL

NULL

58224

62107

58225

62106

3882

78025

80086

78026

80085

2060

My original query over the same table takes 44 seconds and produces the following first few rows.

StartGap

EndGap

FirstAvailable

LastAvailable

AvailableCount

0

2

1

1

1

20624

20626

20625

20625

1

52091

52093

52092

52092

1

55518

55520

55519

55519

1

58222

58224

58223

58223

1

62107

78025

62108

78024

15917

80086

80088

80087

80087

1

80160

80162

80161

80161

1

I've checked the results and my query is showing the correct gaps. Your result line showing 58224 to 62107 represents my end gap result line 5 above to my start gap result line 6 above.

Love to see your revision as certainly looks like it will be very fast.

If you just need to identify IF a list of numbers is in sequence, but not where the gaps are, I believe you can do it in a single pass of the table. (Warning -- to understand this algorithm, some high school math is required!)<FONT color=#008000 size=2>

SELECT LastSysId + 1 AS GapFrom, NextSysId - 1 AS GapTo, NextSysId - (LastSysId + 1) AS GapSizeFROM ( SELECT ( SELECT TOP 1 [SysId] FROM [yourtable] WHERE [SysId] < a.[SysId] ORDER BY [SysId] DESC ) AS LastSysId, a.[SysId] AS NextSysId FROM [yourtable] AS a LEFT JOIN [kicks_medlem_20110630] AS b ON a.[SysId] = b.[SysId] + 1 WHERE b.[SysId] IS NULL) AS aWHERE LastSysId IS NOT NULLORDER BY 3 DESC

Nicely done and I can verify the speed. The only problem is that the code doesn't recognize missing "SysID" of 1. In fact, it doesn't recognize any gap from 1 to x-1 if all the rows between 1 to x-1 are missing. That could be OK for some folks. For me, it's usually not. It's neither wrong nor right. "It Depends" on what it's being used for.

--Jeff Moden"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013