The Dynamic Tally or Numbers Table

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Great article Lynn. It seems that even if you aren't permitted to change the schema that if you are going to use it multiple times you might get better results by putting it into a temp table instead of generating it dynamically over and over.

    Still, if it must be dynamically generated, this is a great technique.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • Jeff Moden

    SSC Guru

    Points: 997204

    timothyawiseman (9/22/2009)


    Great article Lynn. It seems that even if you aren't permitted to change the schema that if you are going to use it multiple times you might get better results by putting it into a temp table instead of generating it dynamically over and over.

    Still, if it must be dynamically generated, this is a great technique.

    True enough. But I've found that the same sites that don't allow a permanent Tally table also have an "angry" DBA the will frequently not allow any TempDB usage nor any access to things like sysColumns in any code... that's where dynamic generation can really save the day especially if it's nasty fast like this one is.

    Heh... here's to "angry" DBA's... I get to charge for the time I take to work around their restrictions. 😛

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • the sqlist

    SSCrazy

    Points: 2398

    Nice article but it has a problem: the function CAN'T be created (or it's code used) on SQL server 2000 because of the CTE approach and and row_number() function; beside that I find the code logic a bit confusing even for experimented sqlists due to its relative complexity.

    Here is another version that WILL work on any SQL version and the code is pretty much self descriptive and easy to understand by everyone. I agree that might not have ALL the features the code in article has but almost all. Still has the start/end/increment values and can be sorted asc desc depending of the needs but the one from my example is limited to 1,000,000 numbers (more than enough vast majority of cases) but it can be easily extended in the code. One little drawback is that you have to order this version of function I built when used:

    if object_id('[dbo].[ufn_Tally_zb]') is not null

    drop function [dbo].[ufn_Tally_zb]

    GO

    CREATE function [dbo].[ufn_Tally_zb](

    @pStartValue int,

    @pEndValue int,

    @pIncrement int

    )

    returns table

    as

    return

    (

    select

    number

    from

    (

    select

    hth.dg*100000+

    tth.dg*10000+

    th.dg*1000+

    h.dg*100+

    t.dg*10+

    u.dg+1 as number

    from

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as tth

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as hth

    ) nr

    where

    number between @pStartValue and @pEndValue

    and number % @pIncrement = 0

    )

    GO

    -- to execute

    select number from [dbo].[ufn_Tally_zb] (1,100000,1) order by number

    I agree though that this is more like an intellectual exercise rather than a needed feature. Is better to use a tally table already created but hey, just in case we need it on the fly.

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Jeff,

    First, leave it to you squeeze another 33% out of a routine, awesome. I'll have to incorporate that change into my code. I think Steve needs to add a disclaimer to the top and bottom of this articel:

    As good as this article is, you really need to read the discussion that follows. It is very informative and enlightening.

    Second, I actually had to take a walk after reading your post. I was extremely humbled, but at the same time flying high like a school kid who just aced the toughest test. Between your praise and being mentioned in the same paragraph as as Itzek, it was just too much. Now I have bring myself down to earth and start working on my next article, one that I hope to submit to SQL Server Standard. Now the bar is set, so I know I really have to make sure I get all my ducks in a row.

    Thank you for being the mentor you are, I don't think I'd be where I am today if it hadn't been for you and SSC. Not knocking the others like Gail, Grant, Gus, Steve, Barry, Jack, Wayne, David, et al., as they too have been great in teaching me new things as well.

  • laughingskeptic

    SSC Rookie

    Points: 39

    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

  • Jeff Moden

    SSC Guru

    Points: 997204

    laughingskeptic (9/22/2009)


    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

    That's nice, but let's see you use that in a join or even as a stand-alone result set.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Lynn Pettis

    SSC Guru

    Points: 442360

    laughingskeptic (9/22/2009)


    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

    Not necessarily true, it really depends on what you are using the Tally table for how big a Tally table you really need.

  • Jeff Moden

    SSC Guru

    Points: 997204

    the sqlist (9/22/2009)


    Nice article but it has a problem: the function CAN'T be created (or it's code used) on SQL server 2000 because of the CTE approach and and row_number() function; beside that I find the code logic a bit confusing even for experimented sqlists due to its relative complexity.

    Here is another version that WILL work on any SQL version and the code is pretty much self descriptive and easy to understand by everyone. I agree that might not have ALL the features the code in article has but almost all. Still has the start/end/increment values and can be sorted asc desc depending of the needs but the one from my example is limited to 1,000,000 numbers (more than enough vast majority of cases) but it can be easily extended in the code. One little drawback is that you have to order this version of function I built when used:

    if object_id('[dbo].[ufn_Tally_zb]') is not null

    drop function [dbo].[ufn_Tally_zb]

    GO

    CREATE function [dbo].[ufn_Tally_zb](

    @pStartValue int,

    @pEndValue int,

    @pIncrement int

    )

    returns table

    as

    return

    (

    select

    number

    from

    (

    select

    hth.dg*100000+

    tth.dg*10000+

    th.dg*1000+

    h.dg*100+

    t.dg*10+

    u.dg+1 as number

    from

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as u

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as t

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as h

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as th

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as tth

    cross join

    ( select 0 as dg union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 ) as hth

    ) nr

    where

    number between @pStartValue and @pEndValue

    and number % @pIncrement = 0

    )

    GO

    -- to execute

    select number from [dbo].[ufn_Tally_zb] (1,100000,1) order by number

    I agree though that this is more like an intellectual exercise rather than a needed feature. Is better to use a tally table already created but hey, just in case we need it on the fly.

    Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂

    The other problem with that method is performance. It takes more than a minute to gen 10Meg rows (after the addition of another cross join) and it takes over a second to generate just 1000 rows. As you said, rather than use such a method on 2k, I'd recommend just using a permanent Tally table... its not worth the cost of having programmable on-the-fly Tally tables in 2k.

    Still, there are some very fast ways to generate on-the-fly tables in 2k...

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997204

    Lynn Pettis (9/22/2009)


    laughingskeptic (9/22/2009)


    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

    Not necessarily true, it really depends on what you are using the Tally table for how big a Tally table you really need.

    Heh... better than that, Lynn... let's see someone use it for something practical and set based. You just can't do a join to the code above.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • the sqlist

    SSCrazy

    Points: 2398

    laughingskeptic (9/22/2009)


    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

    What happened to the good old count:

    declare @i bigint

    select @i=count(*) from largest_table

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • the sqlist

    SSCrazy

    Points: 2398

    Jeff Moden (9/22/2009)

    Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂

    The other problem with that method is performance. It takes more than a minute to gen 10Meg rows (after the addition of another cross join) and it takes over a second to generate just 1000 rows. As you said, rather than use such a method on 2k, I'd recommend just using a permanent Tally table... its not worth the cost of having programmable on-the-fly Tally tables in 2k.

    Still, there are some very fast ways to generate on-the-fly tables in 2k...

    Jeff, I don't know what test you performed because I get totally different numbers. My function has same perfromance results like the one from article. The one from article just took 1:36 min to generate the 10Meg of numbers, same as mine(based on your results). Mine takes 1 sec to generate 100k and not 1k of numbers.

    Try harder. 🙂

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

  • Jeff Moden

    SSC Guru

    Points: 997204

    the sqlist (9/22/2009)


    Jeff Moden (9/22/2009)

    Heh... the logic is simpler but it's still an eyefull, isn't it? 🙂

    The other problem with that method is performance. It takes more than a minute to gen 10Meg rows (after the addition of another cross join) and it takes over a second to generate just 1000 rows. As you said, rather than use such a method on 2k, I'd recommend just using a permanent Tally table... its not worth the cost of having programmable on-the-fly Tally tables in 2k.

    Still, there are some very fast ways to generate on-the-fly tables in 2k...

    Jeff, I don't know what test you performed because I get totally different numbers. My function has same perfromance results like the one from article. The one from article just took 1:36 min to generate the 10Meg of numbers, same as mine(based on your results). Mine takes 1 sec to generate 100k and not 1k of numbers.

    Try harder. 🙂

    I'm using SQL SERVER Profiler with the data produced by the queries routed to a temp table. Routing the output to the screen gives false readings because it takes so long to display the data that it looks like all methods are the same. That's why I called outputting to the screen the "great equalizer".

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Jeff Moden

    SSC Guru

    Points: 997204

    the sqlist (9/22/2009)


    laughingskeptic (9/22/2009)


    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

    What happened to the good old count:

    declare @i bigint

    select @i=count(*) from largest_table

    Again, that's fine.... but what would you do with it? It doesn't produce a list of numbers that can be joined to nor can it be used to spawn things like dates. Instead, it produces just 1 number.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Lynn Pettis

    SSC Guru

    Points: 442360

    Jeff Moden (9/22/2009)


    Lynn Pettis (9/22/2009)


    laughingskeptic (9/22/2009)


    One usualy does not need a tally table larger than the largest table. In which case the following works as a generator:

    declare @i bigint

    set @i = 0

    select @i=@i+1 as num from largest_table

    Not necessarily true, it really depends on what you are using the Tally table for how big a Tally table you really need.

    Heh... better than that, Lynn... let's see someone use it for something practical and set based. You just can't do a join to the code above.

    I guess I should have editted the quote to just the first line. Even with the the phrase "One usually ...", just not very accurate. What if your largest table is only a couple of hundred rows long but you have to parse 2000 character strings on a regular basis?

  • the sqlist

    SSCrazy

    Points: 2398

    Jeff Moden (9/22/2009)

    I'm using SQL SERVER Profiler with the data produced by the queries routed to a temp table. Routing the output to the screen gives false readings because it takes so long to display the data that it looks like all methods are the same. That's why I called outputting to the screen the "great equalizer".

    I guess you're right, here is a much simpler way to test performance though:

    set statistics time on

    select number into #tmp from [dbo].[ufn_Tally_zb] (1,100000,1) order by number

    drop table #tmp

    The CPU time in ms is given in the Message tab. (i forgot to mention this initially)

    Of course, you have to execute both functions using the example template above in different windows and not at the same time. Mine it is much slower indeed becasue creates from start all 1Meg of rows and filters them later. For big numbers though the difference drops drastically.

    However, in order to make the function run on SQL 2000 and earlier you have to use my approach. Actually performance wasn't really my goal here and I asume I could tweak it a little bit. Not today though. 😛

    Don't just give the hungry man a fish, teach him how to catch it as well.

    the sqlist

Viewing 15 posts - 31 through 45 (of 160 total)

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