Porcess Range IDs

• Dan Segalles

  Old Hand

  Points: 392

  More actions

  September 15, 2008 at 11:27 pm

  #872132

  Matt Miller (9/8/2008)

  ---

  It sounds like you have a working solution, but if you want to pursue tracking down the scenarios it doesn't work in, let me know what an example might be. I already have one idea that might be causing an issue.

  Hi Matt,

  Sorry for the very late reply as I was on vacation. Anyway, please find the following scenarios with its respective output:

  CREATE TABLE MyTable

  (

  RowID bigint,

  RowKey int,

  Locale varchar(10),

  Descr nvarchar(1000)

  )

  /**

  * Desc: First Scenario

  * Output: 8,12,16

  **/

  INSERT INTO MyTable (RowID,RowKey,Locale,Descr)

  SELECT '1','5','en','Sample Description 1' UNION ALL

  SELECT '2','5','de','Sample Description 2' UNION ALL

  SELECT '3','4','en','Sample Description 3' UNION ALL

  SELECT '4','3','en','Sample Description 4' UNION ALL

  SELECT '5','2','en','Sample Description 5' UNION ALL

  SELECT '6','1','en','Sample Description 6' UNION ALL

  SELECT '7','1','de','Sample Description 7' UNION ALL

  SELECT '8','2','en','Sample Description 8' UNION ALL

  SELECT '9','3','en','Sample Description 9' UNION ALL

  SELECT '10','5','en','Sample Description 10' UNION ALL

  SELECT '11','4','en','Sample Description 11' UNION ALL

  SELECT '12','3','en','Sample Description 12' UNION ALL

  SELECT '13','2','en','Sample Description 13' UNION ALL

  SELECT '14','1','en','Sample Description 14' UNION ALL

  SELECT '15','3','de','Sample Description 15'

  /**

  * Desc: Second Scenario

  * Output: 2,3,6,9,11,12,14,16

  **/

  INSERT INTO MyTable (RowID,RowKey,Locale,Descr)

  SELECT '1','1','en','Sample Description 1' UNION ALL

  SELECT '2','1','en','Sample Description 2' UNION ALL

  SELECT '3','1','en','Sample Description 3' UNION ALL

  SELECT '4','2','en','Sample Description 4' UNION ALL

  SELECT '5','2','de','Sample Description 5' UNION ALL

  SELECT '6','2','en','Sample Description 6' UNION ALL

  SELECT '7','3','de','Sample Description 7' UNION ALL

  SELECT '8','3','en','Sample Description 8' UNION ALL

  SELECT '9','3','en','Sample Description 9' UNION ALL

  SELECT '10','4','en','Sample Description 10' UNION ALL

  SELECT '11','4','en','Sample Description 11' UNION ALL

  SELECT '12','4','en','Sample Description 12' UNION ALL

  SELECT '13','5','en','Sample Description 13' UNION ALL

  SELECT '14','5','en','Sample Description 14' UNION ALL

  SELECT '15','5','de','Sample Description 15'

  /**

  * Desc: Third Scenario

  * Output: 6,9,10,11,13,16

  **/

  INSERT INTO MyTable (RowID,RowKey,Locale,Descr)

  SELECT '1','1','en','Sample Description 1' UNION ALL

  SELECT '2','2','en','Sample Description 2' UNION ALL

  SELECT '3','3','en','Sample Description 3' UNION ALL

  SELECT '4','4','en','Sample Description 4' UNION ALL

  SELECT '5','5','de','Sample Description 5' UNION ALL

  SELECT '6','1','en','Sample Description 6' UNION ALL

  SELECT '7','2','de','Sample Description 7' UNION ALL

  SELECT '8','3','en','Sample Description 8' UNION ALL

  SELECT '9','3','en','Sample Description 9' UNION ALL

  SELECT '10','3','en','Sample Description 10' UNION ALL

  SELECT '11','3','en','Sample Description 11' UNION ALL

  SELECT '12','4','en','Sample Description 12' UNION ALL

  SELECT '13','4','en','Sample Description 13' UNION ALL

  SELECT '14','5','en','Sample Description 14' UNION ALL

  SELECT '15','5','de','Sample Description 15'

  /**

  * Desc: Fourth Scenario

  * Output: 4,9,10,11,13,16

  **/

  INSERT INTO MyTable (RowID,RowKey,Locale,Descr)

  SELECT '1','1','en','Sample Description 1' UNION ALL

  SELECT '2','2','en','Sample Description 2' UNION ALL

  SELECT '3','3','en','Sample Description 3' UNION ALL

  SELECT '4','1','en','Sample Description 4' UNION ALL

  SELECT '5','2','de','Sample Description 5' UNION ALL

  SELECT '6','3','en','Sample Description 6' UNION ALL

  SELECT '7','1','de','Sample Description 7' UNION ALL

  SELECT '8','2','en','Sample Description 8' UNION ALL

  SELECT '9','3','en','Sample Description 9' UNION ALL

  SELECT '10','3','en','Sample Description 10' UNION ALL

  SELECT '11','3','en','Sample Description 11' UNION ALL

  SELECT '12','1','en','Sample Description 12' UNION ALL

  SELECT '13','1','en','Sample Description 13' UNION ALL

  SELECT '14','2','en','Sample Description 14' UNION ALL

  SELECT '15','3','de','Sample Description 15'

  /**

  * Desc: Fifth Scenario

  * Output: 6,11,12,13,16

  **/

  INSERT INTO MyTable (RowID,RowKey,Locale,Descr)

  SELECT '1','5','en','Sample Description 1' UNION ALL

  SELECT '2','4','en','Sample Description 2' UNION ALL

  SELECT '3','3','en','Sample Description 3' UNION ALL

  SELECT '4','2','en','Sample Description 4' UNION ALL

  SELECT '5','1','de','Sample Description 5' UNION ALL

  SELECT '6','5','en','Sample Description 6' UNION ALL

  SELECT '7','4','de','Sample Description 7' UNION ALL

  SELECT '8','3','en','Sample Description 8' UNION ALL

  SELECT '9','2','en','Sample Description 9' UNION ALL

  SELECT '10','1','en','Sample Description 10' UNION ALL

  SELECT '11','1','en','Sample Description 11' UNION ALL

  SELECT '12','1','en','Sample Description 12' UNION ALL

  SELECT '13','1','en','Sample Description 13' UNION ALL

  SELECT '14','2','en','Sample Description 14' UNION ALL

  SELECT '15','3','de','Sample Description 15'

  Thanks for your time and help.

  -- dans

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  September 16, 2008 at 7:00 am

  #872332

  Here is a CURSOR solution that scales excellent.

  10 times the sample data, 10 times the time.

  SET NOCOUNT ON

  CREATE TABLE#Source

  (

  RowID INT PRIMARY KEY CLUSTERED,

  RowKey INT,

  Locale VARCHAR(10)

  )

  INSERT#Source

  SELECT 1, 5, 'en' UNION ALL

  SELECT 2, 4, 'en' UNION ALL

  SELECT 3, 3, 'en' UNION ALL

  SELECT 4, 2, 'en' UNION ALL

  SELECT 5, 1, 'de' UNION ALL

  SELECT 6, 5, 'en' UNION ALL

  SELECT 7, 4, 'de' UNION ALL

  SELECT 8, 3, 'en' UNION ALL

  SELECT 9, 2, 'en' UNION ALL

  SELECT10, 1, 'en' UNION ALL

  SELECT11, 1, 'en' UNION ALL

  SELECT14, 1, 'en' UNION ALL

  SELECT15, 1, 'en' UNION ALL

  SELECT16, 2, 'en' UNION ALL

  SELECT17, 3, 'de'

  DECLARE@Output TABLE

  (

  RowID INT

  )

  DECLARE@CurrID INT,

  @PrevID INT,

  @RowKey INT,

  @Locale VARCHAR(10)

  SET@PrevID = 1

  DECLAREcurYak CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR

  SELECTRowID,

  RowKey,

  Locale

  FROM#Source

  ORDER BYRowID

  OPENcurYak

  FETCH NEXT FROMcurYak

  INTO @CurrID, @RowKey, @Locale

  WHILE @@FETCH_STATUS = 0

  BEGIN

  IF EXISTS(

  SELECT*

  FROM#Source

  WHERERowID >= @PrevID

  AND RowID < @CurrID

  AND RowKey = @RowKey

  AND Locale = @Locale

  )

  BEGIN

  INSERT@Output

  SELECT@CurrID

  SET@PrevID = @CurrID

  END

  FETCH NEXT FROMcurYak

  INTO @CurrID, @RowKey, @Locale

  END

  CLOSEcurYak

  DEALLOCATEcurYak

  SELECTRowID

  FROM@Output

  UNION ALL

  SELECTMAX(RowID) + 1

  FROM#Source

  DROP TABLE#Source

  ---

  N 56°04'39.16"
  E 12°55'05.25"