Hi Tao, did you post the correct version of your code?
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.
Stephen