Generating a Range

  • Jeff Moden's post using syscolumns gave me inspiration for a simple solution using new features of SQL Server 2005. It is similarly limited to the number of rows in sys.all_columns squared.

    DECLARE @first int, @Last int

    SET @first = 10

    SET @Last = 500000

    SELECT TOP (@last - @first + 1) ROW_NUMBER() OVER(ORDER BY a.object_id) + @first - 1 AS Number

    FROM master.sys.all_columns a, master.sys.all_columns b

  • David wrote... "If I was to use the static table approach I would probably stick it in its own database so that it wouldn't artificially inflate the backups."

    Yep, I know what you mean but I thought your article was awesome... you had the hair to do a comparison on a difficult subject.  Well, done.

    Matt, thanks for your post... I didn't realize that 2005 had incorporated an Oracle like Row_Number () Over command... now, that's useful!  Because of the order by, I have to ask, how's the performance?

    --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)
    Intro to Tally Tables and Functions

  • Hi,

    I tested my modification to the function for the aforementioned range (5,567720) and got: f2:9986 and f3:10183 in MS. For my range: (211000,339999,10) I can't reliably measure the difference.

    I wanted to add steps to the function:

    ALTER   FUNCTION dbo.fnCrossJoinRange3 (

     @first int,  --##PARAM @first The lowest value in the range.

     @last int,  --##PARAM @Last  The highest value in the range.

     @step int --##PARAM @step  The increment value.

    RETURNS @values TABLE ( value int primary key ) AS 

    BEGIN

    declare @min-2 int

    set @min-2 = @first

    set @Last = (@last - @first) / @step

    set @first = 0

    INSERT INTO @values(value)

    SELECT @min +

           (@step *

            ( -- raw range

             units.value +

     (tens.value) +

     (hundreds.value ) +

     (Thousands.value ) +

     (TenThousands.value ) +

     (CThousands.value ) +

     (Millions.value )

              -- raw range

            )

           )

    FROM dbo.Digits units

    CROSS JOIN (SELECT value * 10 as value from dbo.Digits WHERE value * 10 <=@last) tens

    CROSS JOIN (SELECT value * 100 as value  from dbo.Digits WHERE value * 100 <=@last) hundreds

    CROSS JOIN (SELECT value * 1000 as value  from dbo.Digits WHERE value * 1000 <=@last) Thousands

    CROSS JOIN (SELECT value * 10000 as value  from dbo.Digits WHERE value * 10000 <=@last) TenThousands

    CROSS JOIN (SELECT value * 100000 as value  from dbo.Digits WHERE value * 100000 <=@last) CThousands

    CROSS JOIN (SELECT value * 1000000 as value  from dbo.Digits WHERE value * 1000000 <=@last) Millions

    where units.value +

     (tens.value ) +

     (hundreds.value) +

     (Thousands.value ) +

     (TenThousands.value ) +

     (CThousands.value ) +

     (Millions.value )

    BETWEEN @first and @Last

    RETURN

    END

    GO

  • Hi,

    Based on all the replies I put together the following (just to compare and it was a slow day at the office):

    1. A function that might not be the fastest of all (bit better performance than fn_nums) but the most comprehensive. It includes negative ranges (limited to range of an int), a step value and consists of a combination of using a a form of the original cross join function, identity values and incremental inserts.

    2. A modification of the original Cross Join Function fnUnionRange using computed columns which improves performance a bit.

    The first function consists of 3 functions

    create function dbo.fn_zero_to_giga()

    returns @basevalues table

    (

     unit   int not null primary key,

     deca   as ( unit * 10 ),

     hecto  as ( unit * 100 ),

     kilo   as ( unit * 1000 ),

     dakilo as ( unit * 10000 ),

     hkilo  as ( unit * 100000 ),

     mega   as ( unit * 1000000 ),

     damega as ( unit * 10000000 ),

     hmega  as ( unit * 100000000 ),

     giga   as ( case when unit < 3 then unit * 1000000000 else 0 end )

    )

    as

    begin

    insert @basevalues

    select 0 as value

    union all

    select 1

    union all

    select 2

    union all

    select 3

    union all

    select 4

    union all

    select 5

    union all

    select 6

    union all

    select 7

    union all

    select 8

    union all

    select 9

    return

    end

    go

    create function dbo.fn_int_range_tab_base

    (

     @p_startnum  int = 1,

     @p_endnum    int = 1,

     @p_increment int = 1

    returns @rangetab table ( idx int identity(-2147483648,1) primary key, val int )

    as

    begin

    -- Validate the start and end numbers

    set @p_startnum = isnull( @p_startnum, 1 )

    set @p_endnum = isnull( @p_endnum, 1 )

    set @p_increment = isnull( @p_increment, 1 )

    declare @order smallint

    declare @val int

    declare @currcnt int

    declare @endidx int

    declare @adjendnum int

    set @order = 1

    -- Swap around the values if required and set the order flag

    if @p_startnum > @p_endnum begin

      set @order = -1

      set @adjendnum = @p_startnum

      set @p_startnum = @p_endnum

      set @p_endnum = @adjendnum

    end --if

    set @adjendnum = @p_startnum + ( ( ( ( @p_endnum - @p_startnum ) / @p_increment ) ) * @p_increment )

    set @endidx = -2147483648 + ( ( ( @adjendnum - @p_startnum ) / @p_increment ) + 1 )

    insert @rangetab

    select 1 as value

    from ( select unit   as value from dbo.fn_zero_to_giga() where -2147483648 + unit   <= @endidx ) as u,

         ( select deca   as value from dbo.fn_zero_to_giga() where -2147483648 + deca   <= @endidx ) as da,

         ( select hecto  as value from dbo.fn_zero_to_giga() where -2147483648 + hecto  <= @endidx ) as h,

         ( select kilo   as value from dbo.fn_zero_to_giga() where -2147483648 + kilo   <= @endidx ) as k,

         ( select dakilo as value from dbo.fn_zero_to_giga() where -2147483648 + dakilo <= @endidx ) as dak,

         ( select hkilo  as value from dbo.fn_zero_to_giga() where -2147483648 + hkilo  <= @endidx ) as hk

    where -2147483648 + u.value + da.value + h.value + k.value + dak.value + hk.value + 1 <= @endidx

    set @currcnt = @@rowcount

    while @currcnt < ( ( ( @adjendnum - @p_startnum ) / @p_increment ) + 1 ) begin

      if @@identity + @currcnt < @endidx begin

        -- Avoid using the where clause on large ranges

        insert @rangetab

        select val

          from @rangetab

      end --if

      else begin

        insert @rangetab

        select val

          from @rangetab

         where idx + @currcnt < @endidx

      end --else

      set @currcnt = @currcnt + @@rowcount

    end --else

    if @p_increment > 1 begin

      if @order = 1 begin

        set @val = @p_startnum - @p_increment

      end --if

      else begin

        set @val = @adjendnum + @p_increment

      end --else

     

      set @p_increment = @p_increment * @order

     

      update @rangetab

         set @val = val = @val + @p_increment

    end --if

    return

    end

    go

    create function dbo.fn_int_range_tab

    (

     @p_startnum  int = 1,

     @p_endnum    int = 1,

     @p_increment int = 1

    )

    returns table

    as

    return

    (

    select case isnull( @p_increment, 1 ) when 1 then ( idx - -2147483648 ) + @p_startnum else val end as val

      from dbo.fn_int_range_tab_base( @p_startnum, @p_endnum, @p_increment )

    )

    go

    The second function uses the fn_zero_to_giga function above to make it a bit more readable and to improve performance using computed columns. It could be modified to use a 10 row permanent table instead of a table variable to give a bit of a performance increase.

    create function dbo.fnUnionRangeNew1

    (

     @first int ,

     @last  int

    returns table

    as

    return

    (

    select u.value + da.value + h.value + k.value + dak.value + hk.value + m.value as value

    from ( select unit   as value from dbo.fn_zero_to_giga() where unit   <= @Last ) as u,

         ( select deca   as value from dbo.fn_zero_to_giga() where deca   <= @Last ) as da,

         ( select hecto  as value from dbo.fn_zero_to_giga() where hecto  <= @Last ) as h,

         ( select kilo   as value from dbo.fn_zero_to_giga() where kilo   <= @Last ) as k,

         ( select dakilo as value from dbo.fn_zero_to_giga() where dakilo <= @Last ) as dak,

         ( select hkilo  as value from dbo.fn_zero_to_giga() where hkilo  <= @Last ) as hk,

         ( select mega   as value from dbo.fn_zero_to_giga() where mega   <= @Last ) as m

    where u.value + da.value + h.value + k.value + dak.value + hk.value + m.value between @first and @Last

    )

    go

Viewing 4 posts - 16 through 18 (of 18 total)

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