tsql - In SQL can a sequenced range selection be done more efficiently than my algorithm (see code) that uses a cursor? -

I have to collapse several categories of sequential numbers (1 or more) in sets of minimum and maximum values. I do not have a unique integer (a duplicate) in the table column.

To solve this problem, clearly (me) is using a cursor (below) See my algorithm) and run it again through every integer. However, it seems to be incomplete for me, so I'm wondering if there is a more efficient algorithm perhaps a method of using normal table expression with recursive though I have more than 32767 integers, so any solution to the Options (MAXRECURSION 0) which sets unlimited recursions.

A simplified test case for my existing algorithm is a cursor for each sequence of sequential numbers (such as 1-3, 9-11, 13-13, 15-16) To produce the minimum and maximum.

I am using MS SQL Server 2008. Please leave a comment with two dashes (-).

  declared @minInt int, @maxInt int declare @nextInt int, @prevInt int declared a temporary table to store a range of @ RangeTable table (minInt int, maxInt int ) To select * mycursor declares the cursor (select ID union as 1 select ID 2 as ID union selection 3 as ID union selection 9 as ID union chooses 10 as ED chooses the association 11 As the ED chooses the Union 13 ID union as the ID union, the selection of 16 ED is to be selected as per id-order, tblRanges order is required for this algorithm if used with normal data, opens MacCars - to start a new sequence, select @minInt from mycursor @maxInt = @ MinInt- Select the minimum and minimum maximum value @prevInt = @ minInt - declare the last int @sequenceFound int while @@ FETCH_STATUS = 0 starts @ Select Sequence FOUND = 1 - Correct the default flag value - Set to loop when Get the sequence when matching @@ FETCH_STATUS = 0 and @sequenceFound = 1 and start with mycursor in @nextInt if @nextInt = (@prevInt + 1) starts @sequenceFound = 1 end select and start again @sequenceFound = 0 end select Select @prevInt = @ nextInt - Store the current value as the previous value for the next comparison if @sequenceFound = 1 - If the next part is part of a sequence, then the new maximeter and @maximite ; @ Next int - indexed output must always be correct for duplicates, in which the selection of @maxInt = @nextInt end is starting - while sequence - found-store sequence series and then check for more series @rangeTable ( MinInt, maxInt) (= minint, @ maxInt) - Store the current sequence as the new Minimum and Maximit for next sequence repeat @minInt = @nextInt @maxInt = @nextInt end choice Do not - While more table rows are selected from @RangeTable close mycursor  
 
  
 
  
 
  Select the ID union as the ID union select 10 as  select the ID union as the selection of the union ID select 2 AS ID union as 3 selects as  ID union as selection of ID union as selection of ID union 15 as ID union selected 16 AS ID), as the starting point (selection ID, ROW_NUMBER (), depending on ID (depending on ID) Rownum no one exists (where to select B * BID = AID - 1 *), Ending Points AS (Selection ID, ROW_NUMBER () ID (According to ID) as TLRNGS In an end where the EXISTS (SELECT * FROM tblRanges as B WHERE BID = AID + 1)) SELECT SID as start_range, end points from E.ID as the end points at the end points E. E.Rownum = at S.rownum;  

You can read the full description from your chapter in the SQL Saver MVP Deep Duos . He interprets various techniques (including cursors) and compares them in terms of performance.