Generate sequence of numbers between values

  • Hi all,

    Can someone please recommend an efficient way to achieve the following. Thank you.

    -- sample date

    DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    SELECT * FROM @rng-2

    -- expected result

    select 123 AS Id, 120000006660 AS NR, 5 AS Category union all

    select 123,120000006661,5 union all

    select 123,120000006662,5 union all

    select 123,120000006663,5 union all

    select 123,120000006664,5 union all

    select 123,120000006665,5 union all

    select 123,120000006666,5 union all

    select 123,120000006667,5 union all

    select 123,120000006668,5 union all

    select 123,120000006669,5 union all

    select 123,120000006670,5 union all

    select 123,120000006671,5 union all

    select 123,120000006672,5 union all

    select 123,120000006673,5 union all

    select 123,120000006674,5 union all

    select 123,120000006675,5 union all

    select 123,120000006676,5 union all

    select 123,120000006677,5 union all

    select 123,120000006678,5 union all

    select 123,120000006679,5 union all

    select 123,120000006680,5 union all

    select 123,120000006681,5 union all

    select 123,120000006682,5 union all

    select 123,120000006683,5 union all

    select 123,120000006684,5 union all

    select 123,120000006685,5 union all

    select 123,120000006686,5 union all

    select 123,120000006687,5 union all

    select 123,120000006688,5 union all

    select 123,120000006689,5 union all

    select 123,120000006690,5 union all

    select 123,120000011660,5 union all

    select 123,120000011661,5 union all

    select 123,120000011662,5 union all

    select 123,120000011663,5 union all

    select 123,120000011664,5 union all

    select 123,120000011665,5 union all

    select 123,120000011666,5 union all

    select 123,120000011667,5 union all

    select 123,120000011668,5 union all

    select 123,120000011669,5 union all

    select 123,120000011670,5 union all

    select 123,199000011660,10 union all

    select 123,199000011661,10 union all

    select 123,199000011662,10 union all

    select 123,199000011663,10 union all

    select 123,199000011664,10 union all

    select 123,199000011665,10 union all

    select 123,199000011666,10 union all

    select 123,199000011667,10 union all

    select 123,199000011668,10 union all

    select 123,199000011669,10 union all

    select 123,199000011670,10 union all

    select 789,88800001060,5 union all

    select 789,88800001061,5 union all

    select 789,88800001062,5 union all

    select 789,88800001063,5 union all

    select 789,88800001064,5 union all

    select 789,88800001065,5 union all

    select 789,88800001066,5 union all

    select 789,88800001067,5 union all

    select 789,88800001068,5 union all

    select 789,88800001069,5 union all

    select 789,88800001070,5 union all

    select 789,88800001071,5 union all

    select 789,88800001072,5 union all

    select 789,88800001073,5 union all

    select 789,88800001074,5 union all

    select 789,88800001075,5 union all

    select 789,88800001076,5 union all

    select 789,88800001077,5 union all

    select 789,88800001078,5 union all

    select 789,88800001079,5 union all

    select 789,88800001080,5 union all

    select 789,88800001081,5 union all

    select 789,88800001082,5 union all

    select 789,88800001083,5 union all

    select 789,88800001084,5 union all

    select 789,88800001085,5 union all

    select 789,88800001086,5 union all

    select 789,88800001087,5 union all

    select 789,88800001088,5 union all

    select 789,88800001089,5 union all

    select 789,88800001090,5 union all

    select 789,99900011660,10 union all

    select 789,99900011661,10 union all

    select 789,99900011662,10 union all

    select 789,99900011663,10 union all

    select 789,99900011664,10 union all

    select 789,99900011665,10 union all

    select 789,99900011666,10 union all

    select 789,99900011667,10 union all

    select 789,99900011668,10 union all

    select 789,99900011669,10 union all

    select 789,99900011670,10 union all

    select 789,99900011671,10 union all

    select 789,99900011672,10 union all

    select 789,99900011673,10 union all

    select 789,99900011674,10 union all

    select 789,99900011675,10 union all

    select 789,10000011560,15 union all

    select 789,10000011561,15 union all

    select 789,10000011562,15 union all

    select 789,10000011563,15 union all

    select 789,10000011564,15 union all

    select 789,10000011565,15 union all

    select 789,10000011566,15 union all

    select 789,10000011567,15 union all

    select 789,10000011568,15 union all

    select 789,10000011569,15 union all

    select 789,10000011570,15 union all

    select 789,10000011571,15 union all

    select 789,10000011572,15 union all

    select 789,10000011573,15 union all

    select 789,10000011574,15 union all

    select 789,10000011575,15 union all

    select 789,10000011576,15 union all

    select 789,10000011577,15 union all

    select 789,10000011578,15 union all

    select 789,10000011579,15 union all

    select 789,10000011580,15 union all

    select 789,10000011581,15 union all

    select 789,10000011582,15 union all

    select 789,10000011583,15 union all

    select 789,10000011584,15 union all

    select 789,10000011585,15 union all

    select 789,10000011586,15 union all

    select 789,10000011587,15 union all

    select 789,10000011588,15 union all

    select 789,10000011589,15 union all

    select 789,10000011590,15 union all

    select 789,10000011591,15 union all

    select 789,10000011592,15 union all

    select 789,10000011593,15 union all

    select 789,10000011594,15 union all

    select 789,10000011595,15 union all

    select 789,10000011596,15 union all

    select 789,10000011597,15 union all

    select 789,10000011598,15 union all

    select 789,10000011599,15 union all

    select 789,10000011600,15 union all

    select 789,10000011601,15 union all

    select 789,10000011602,15 union all

    select 789,10000011603,15 union all

    select 789,10000011604,15 union all

    select 789,10000011605,15 union all

    select 789,10000011606,15 union all

    select 789,10000011607,15 union all

    select 789,10000011608,15 union all

    select 789,10000011609,15 union all

    select 789,10000011610,15 union all

    select 789,10000011611,15 union all

    select 789,10000011612,15 union all

    select 789,10000011613,15 union all

    select 789,10000011614,15 union all

    select 789,10000011615,15 union all

    select 789,10000011616,15 union all

    select 789,10000011617,15 union all

    select 789,10000011618,15 union all

    select 789,10000011619,15 union all

    select 789,10000011620,15 union all

    select 789,10000011621,15 union all

    select 789,10000011622,15 union all

    select 789,10000011623,15 union all

    select 789,10000011624,15 union all

    select 789,10000011625,15 union all

    select 789,10000011626,15 union all

    select 789,10000011627,15 union all

    select 789,10000011628,15 union all

    select 789,10000011629,15 union all

    select 789,10000011630,15 union all

    select 789,10000011631,15 union all

    select 789,10000011632,15 union all

    select 789,10000011633,15 union all

    select 789,10000011634,15 union all

    select 789,10000011635,15 union all

    select 789,10000011636,15 union all

    select 789,10000011637,15 union all

    select 789,10000011638,15 union all

    select 789,10000011639,15 union all

    select 789,10000011640,15 union all

    select 789,10000011641,15 union all

    select 789,10000011642,15 union all

    select 789,10000011643,15 union all

    select 789,10000011644,15 union all

    select 789,10000011645,15 union all

    select 789,10000011646,15 union all

    select 789,10000011647,15 union all

    select 789,10000011648,15 union all

    select 789,10000011649,15 union all

    select 789,10000011650,15 union all

    select 789,10000011651,15 union all

    select 789,10000011652,15 union all

    select 789,10000011653,15 union all

    select 789,10000011654,15 union all

    select 789,10000011655,15 union all

    select 789,10000011656,15 union all

    select 789,10000011657,15 union all

    select 789,10000011658,15 union all

    select 789,10000011659,15 union all

    select 789,10000011660,15 union all

    select 789,10000011661,15 union all

    select 789,10000011662,15 union all

    select 789,10000011663,15 union all

    select 789,10000011664,15 union all

    select 789,10000011665,15 union all

    select 789,10000011666,15 union all

    select 789,10000011667,15 union all

    select 789,10000011668,15 union all

    select 789,10000011669,15 union all

    select 789,10000011670,15 union all

    select 258,599900010660,10 union all

    select 258,599900010661,10 union all

    select 258,599900010662,10 union all

    select 258,599900010663,10 union all

    select 258,599900010664,10 union all

    select 258,599900010665,10 union all

    select 258,599900010666,10 union all

    select 258,599900010667,10 union all

    select 258,599900010668,10 union all

    select 258,599900010669,10 union all

    select 258,599900010670,10 union all

    select 258,599900010671,10 union all

    select 258,599900010672,10 union all

    select 258,599900010673,10 union all

    select 258,599900010674,10 union all

    select 258,599900010675,10 union all

    select 258,599900010676,10 union all

    select 258,599900010677,10 union all

    select 258,599900010678,10 union all

    select 258,599900010679,10 union all

    select 258,599900010680,10 union all

    select 258,599900010681,10 union all

    select 258,599900010682,10 union all

    select 258,599900010683,10 union all

    select 258,599900010684,10 union all

    select 258,599900010685,10 union all

    select 258,510000010560,10 union all

    select 258,510000010561,10 union all

    select 258,510000010562,10 union all

    select 258,510000010563,10 union all

    select 258,510000010564,10 union all

    select 258,510000010565,10

  • Read this article:

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    You'll find your solution in there.

    _____________
    Code for TallyGenerator

  • Hi, Thanks for your response. Not sure if I want to follow that approach due to the BIGINT data types. I'm looking for a simple way of doing this, performance doesn't really matter as the stored procedure would get executed only a couple of times a week.

  • I don't understand what the bigints have to do with it. For a real or virtual table of numbers that goes from 1-N:

    select startnum + (num-1)

    from numtable

    where num <= rangevalue

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • I think using a while loop this should be achievable

    DECLARE @rng-2 TABLE ( seq int identity(1,1),Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    DECLARE @RNG_output TABLE ( Id INT, Range_No BIGINT, Category INT)

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    select * from @rng-2

    -- Define variables

    Declare @MinValue int

    Declare @MaxValue int

    Declare @Counter int

    Declare @id int

    Declare @RangeStart BIGINT

    Declare @RangeEnd BIGINT

    Declare @Category int

    Declare @NumberCount int

    Select @MaxValue = max (seq) from @rng-2

    Select @counter = 1

    --Print 'The max value ' +convert(varchar,@MaxValue )

    While ( @counter <= @MaxValue)

    Begin

    Select

    @id = id

    ,@RangeStart= RangeStart

    ,@RangeEnd= RangeEnd

    ,@Category = Category

    ,@NumberCount = NumberCount

    from @rng-2 where seq = @counter

    --Print 'The @counter value ' +convert(varchar,@counter )

    While (@RangeStart <= @RangeEnd )

    Begin

    --Print '@RangeStart ' + convert(varchar,@RangeStart)

    INSERT INTO @RNG_output (id,Range_No,Category)

    SELECT @id ,@RangeStart,@Category

    Select @RangeStart = @RangeStart + 1

    End

    Select @counter = @counter+ 1

    -- Resetting the values

    Select

    @id = 0

    ,@RangeStart= 0

    ,@RangeEnd= 0

    ,@Category = 0

    ,@NumberCount = 0

    End

    select * from @RNG_output

  • First, below is a simple and easy solution that is much less complicated than a loop-based, multi-variable solution. It also performs quite well (for when performance does matter).

    DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    SELECT * FROM @rng-2;

    WITH

    L1 AS

    (

    SELECT C=1

    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(c)

    ),

    iTally AS

    (

    SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1

    FROM L1 a, L1 b, L1 c, L1 d

    )

    SELECT Id, NR = RangeStart+N, Category

    FROM @rng-2

    CROSS APPLY

    (

    SELECT TOP(NumberCount) N

    FROM iTally

    ) i;

    I would recommend that you give that article that Sergiy posted about numbers/tally tables - it will change your career: http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • kiril.lazarov.77 (12/1/2015)


    Hi, Thanks for your response. Not sure if I want to follow that approach due to the BIGINT data types. I'm looking for a simple way of doing this, performance doesn't really matter as the stored procedure would get executed only a couple of times a week.

    Heh... listen to you. Did you or did you not start this post off with the following?

    kiril.lazarov.77 (12/1/2015)


    Hi all,

    Can someone please recommend an [font="Arial Black"]efficient [/font]way to achieve the following. Thank you.

    Why do people always say "performance doesn't really matter" as soon as they have to learn something new? And, trust me, unless you're making up these ranges yourself, you have no control over them and there may be a day when you get a load with many large ranges. That's when you'll wish you did it right the first time. Performance ALWAYS matters just as much as getting correct answers from your code.

    Speaking of "correct", your test data has negative ranges. Was that accidental or is that the way you're really getting data? Here's the code that proves it. Look for the negative RANGEs.

    -- sample date

    DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    ;

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    ;

    SELECT RANGE = d.RangeEnd-d.RangeStart+1,* FROM @rng-2 d;

    Assuming that you're not supposed to get negative ranges, here's the corrected test data with 3 larger ranges included for a total of 3,000,243 rows.

    -- sample date

    DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    ;

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99900011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599900010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6 UNION ALL

    SELECT 998, 210000000000, 210000999999, 1, 99 UNION ALL

    SELECT 998, 210001000000, 210001999999, 2, 99 UNION ALL

    SELECT 998, 210002000000, 210002999999, 3, 99

    ;

    --===== Show the size of the ranges, just because...

    SELECT RANGE = d.RangeEnd-d.RangeStart+1,* FROM @rng-2 d;

    Let's get "Efficient". You're going to need it for other things in the future so you might as well just go ahead and install the following iTVF.

    CREATE FUNCTION [dbo].[fnTally]

    /**********************************************************************************************************************

    Purpose:

    Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.

    As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.

    Usage:

    --===== Syntax example (Returns BIGINT)

    SELECT t.N

    FROM dbo.fnTally(@ZeroOrOne,@MaxN) t

    ;

    Notes:

    1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.

    Refer to the following URLs for how it works and introduction for how it replaces certain loops.

    http://www.sqlservercentral.com/articles/T-SQL/62867/

    http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers

    2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type

    will cause the sequence to start at 1.

    3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.

    5. If @MaxN is negative or NULL, a "TOP" error will be returned.

    6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger

    number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with

    that many values, you should consider using a different tool. ;-)

    7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending

    sort is required, use code similar to the following. Performance will decrease by about 27% but it's still

    very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.

    If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.

    DECLARE @MaxN BIGINT;

    SELECT @MaxN = 1000;

    SELECT DescendingN = @MaxN-N+1

    FROM dbo.fnTally(1,@MaxN);

    8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    Revision History:

    Rev 00 - Unknown - Jeff Moden

    - Initial creation with error handling for @MaxN.

    Rev 01 - 09 Feb 2013 - Jeff Moden

    - Modified to start at 0 or 1.

    Rev 02 - 16 May 2013 - Jeff Moden

    - Removed error handling for @MaxN because of exceptional cases.

    Rev 03 - 22 Apr 2015 - Jeff Moden

    - Modify to handle 1 Trillion rows for experimental purposes.

    **********************************************************************************************************************/

    (@ZeroOrOne BIT, @MaxN BIGINT)

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN WITH

    E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1) --10E1 or 10 rows

    , E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows

    ,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows

    SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.

    UNION ALL

    SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN

    ;

    GO

    Then your little problem never becomes a big problem and the code becomes child's play.

    --===== Generate the 3,000,243 rows "Efficiently".

    SELECT d.Id

    ,NR = RangeStart+t.N

    ,Category

    FROM @rng-2 d

    CROSS APPLY dbo.fnTally(0,(d.RangeEnd-d.RangeStart)) t

    ;

    That'll generate all 3,000,243 rows to the screen in just 16 seconds. If you use SELECT INTO, it'll create a table with all those rows in it in just over a second.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • himanshu.sinha (12/1/2015)


    I think using a while loop this should be achievable

    DECLARE @rng-2 TABLE ( seq int identity(1,1),Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    DECLARE @RNG_output TABLE ( Id INT, Range_No BIGINT, Category INT)

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    select * from @rng-2

    -- Define variables

    Declare @MinValue int

    Declare @MaxValue int

    Declare @Counter int

    Declare @id int

    Declare @RangeStart BIGINT

    Declare @RangeEnd BIGINT

    Declare @Category int

    Declare @NumberCount int

    Select @MaxValue = max (seq) from @rng-2

    Select @counter = 1

    --Print 'The max value ' +convert(varchar,@MaxValue )

    While ( @counter <= @MaxValue)

    Begin

    Select

    @id = id

    ,@RangeStart= RangeStart

    ,@RangeEnd= RangeEnd

    ,@Category = Category

    ,@NumberCount = NumberCount

    from @rng-2 where seq = @counter

    --Print 'The @counter value ' +convert(varchar,@counter )

    While (@RangeStart <= @RangeEnd )

    Begin

    --Print '@RangeStart ' + convert(varchar,@RangeStart)

    INSERT INTO @RNG_output (id,Range_No,Category)

    SELECT @id ,@RangeStart,@Category

    Select @RangeStart = @RangeStart + 1

    End

    Select @counter = @counter+ 1

    -- Resetting the values

    Select

    @id = 0

    ,@RangeStart= 0

    ,@RangeEnd= 0

    ,@Category = 0

    ,@NumberCount = 0

    End

    select * from @RNG_output

    As While loops go, that one's pretty fast. It only takes a little over a minute to return all the 3,000,243 rows to the screen using the test data that I used in my previous post above.

    When you absolutely must use a While loop (and this case isn't one of those, just to be sure), you can do a couple of things to make it a bit faster. First, use SET NOCOUNT ON. It's not a big thing but it'll cut out a couple of seconds generating all of the singleton row counts. The other thing is to put your outer WHILE loop in an explicit transaction and COMMIT it right after the END of the outer While loop. It doesn't help a huge amount (about 25%) in this case because of the Table Variable and the fact that I have enough memory so it doesn't spool to disk but if need to write to disk, it'll save a lot especially in reducing the time it writes to the log file.

    Again, though, it's usually easier to write set based code with a Tally Table or a Tally Function in play. Consider not writing loops for these kinds of things.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .

  • Thank you very much for taking the time to respond. Works perfectly.

  • himanshu.sinha (12/1/2015)


    Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .

    A better way would be to avoid the loop.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (12/3/2015)


    himanshu.sinha (12/1/2015)


    Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .

    A better way would be to avoid the loop.

    Amen to that. There are cases where a loop is required, but this is certainly not one of them.

  • Jeff Moden (12/3/2015)


    himanshu.sinha (12/1/2015)


    Thanks Jeff for the idea of putting the while loop in TRAN , I think I understand the logic behind that . I hope if anyone who uses the code will make the necessary updates .

    A better way would be to avoid the loop.

    Jeff, just one change I would make for code running on SQL Server 2008 and newer (yes, I realize people still need to be able support SQL Server 2005):

    WITH

    E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(N)) --10E1 or 10 rows

    , E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows

    ,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows

    SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.

    UNION ALL

    SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN

    ;

  • Recursive Solution

    -- sample date

    DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599000010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    -- Recursive Solution

    -- Should be used carefully

    -- Only if gap between ranges wont be massive

    ;WITH dataCTE AS

    (

    SELECT Id

    , NR = RangeStart

    , RangeEnd

    , Category

    FROM @rng-2

    UNION ALL

    SELECT Id

    , NR = NR + 1

    , RangeEnd

    , Category

    FROM dataCTE

    WHERE NR < RangeEnd

    )

    SELECT Id

    , NR

    , Category

    FROM dataCTE

    OPTION (MAXRECURSION 1000)

    I also noticed a data issue in below :-

    Start > End

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

  • Aditya Daruka (12/3/2015)


    Recursive Solution

    -- sample date

    DECLARE @rng-2 TABLE (Id INT, RangeStart BIGINT, RangeEnd BIGINT, Category INT, NumberCount INT)

    INSERT INTO @rng-2

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599000010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    -- Recursive Solution

    -- Should be used carefully

    -- Only if gap between ranges wont be massive

    ;WITH dataCTE AS

    (

    SELECT Id

    , NR = RangeStart

    , RangeEnd

    , Category

    FROM @rng-2

    UNION ALL

    SELECT Id

    , NR = NR + 1

    , RangeEnd

    , Category

    FROM dataCTE

    WHERE NR < RangeEnd

    )

    SELECT Id

    , NR

    , Category

    FROM dataCTE

    OPTION (MAXRECURSION 1000)

    I also noticed a data issue in below :-

    Start > End

    SELECT 123, 120000006660, 120000006690, 5, 31 UNION ALL

    SELECT 123, 120000011660, 120000011670, 5, 11 UNION ALL

    SELECT 123, 199000011660, 199000011670, 10, 11 UNION ALL

    SELECT 789, 88800001060, 88800001090, 5, 31 UNION ALL

    SELECT 789, 99900011660, 99000011675, 10, 16 UNION ALL

    SELECT 789, 10000011560, 10000011670, 15, 111 UNION ALL

    SELECT 258, 599900010660, 599000010685, 10, 26 UNION ALL

    SELECT 258, 510000010560, 510000010565, 10, 6

    This will be much slower than the tally table solutions already presented.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply