SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Dynamic Tally or Numbers Table


The Dynamic Tally or Numbers Table

Author
Message
Silverfox
Silverfox
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 1161
I like the article but just a minor point, the code doesnt work, maybe it is the way it came across when formatted.

You are missing a space, in your set statistics statements
in the code blocks like ioon instead of "io on" for example

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52328 Visits: 38684
Silverfox (9/22/2009)
I like the article but just a minor point, the code doesnt work, maybe it is the way it came across when formatted.

You are missing a space, in your set statistics statements
in the code blocks like ioon instead of "io on" for example


Actually, I noticed that and checked my original that I submitted to ssc. The code there is fine, I think it has some thing to do with the process used to prepare it for publication.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Silverfox
Silverfox
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 1161
Lynn Pettis (9/22/2009)
Silverfox (9/22/2009)
I like the article but just a minor point, the code doesnt work, maybe it is the way it came across when formatted.

You are missing a space, in your set statistics statements
in the code blocks like ioon instead of "io on" for example


Actually, I noticed that and checked my original that I submitted to ssc. The code there is fine, I think it has some thing to do with the process used to prepare it for publication.


np, maybe something you can nag steve about :-P, nice article btw, going to take a closer more detailed look when I get a moment.

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52328 Visits: 38684
dbishop (9/22/2009)
I am a bit perplexed by the article. I buiul a tally table in each of my databases a couple of years ago. Static table, 100K rows, PK. When I initially built it I really did not care if it took 150ms or 1.3 seconds, because it was a one-time thing. A tally table is so useful, it should be part of the SQL installation and be included when a new database is created.

An article on how to use a tally table would have been a lot better than how to create one. Likle it was said, there are a lot of articles that provide a 5-line code solution on how to create it all over the place. No reason to be creating one on the fly every time you need it.


Curious, what happens if you find yourself in a shop where you aren't allowed to create a static tally table, what are you going to do? This is a problem we see quite frequently on the forums, as Luke mentions above, when a tally table solution is suggested.

In addition, this article may make a little more sense if you take to the time to read the article I also referenced that was published in the May 2009 edition of SQL Server Magazine. This article is in response to that article, demonstrating that there is a better way to generate a dynamic tally table.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Rob Fisk
Rob Fisk
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 428
dbishop. See the reply to my first post above regarding the times where a static tally table is not possible.

The article is not about creating a tally table but creating a dynamic equivalent and on those merits it's great.

If you can create a static one then marvelous. If not and you need a dynamic way of achieving the same results each time you need to then performance is everything.

_______________________________________________________
Change is inevitable... Except from a vending machine.

Mohit Nayyar
Mohit Nayyar
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 94
Great Article Lynn

I also wrote something similar long time back on my blog....

http://mohitnayyar.blogspot.com/2008/09/data-extrapolation-on-fly.html


Thanks
Mohit

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"
peter-757102
peter-757102
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1079 Visits: 2559
I wonder why a SQL magazine published code that used a multiline table valued function given their reputation for scaling bad. It will not help to educate readers on how to make the most of their platform. Lynn Pettis version is for that reason alone a good contribution, now have it published too Wink

After copying I cleaned up the code on my system as the article has many keywords merged, like “innerjoin” instead of “inner join” and “casewhen” instead of “case when”. I understand all that is happening, except for one thing…the ordered part of the output and more especially…why.

I understand how the numbers come to be, but am uncertain what good the order is in an in-line function. To my knowledge no order assumption can be made by the consuming code, unless something is at work here I don’t know or realize yet.

As for performance I would have to run many tests, especially how it interacts in more complex operations that usually involve a traditional tally table for speedup.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52328 Visits: 38684
peter-757102 (9/22/2009)
I wonder why a SQL magazine published code that used a multiline table valued function given their reputation for scaling bad. It will not help to educate readers on how to make the most of their platform. Lynn Pettis version is for that reason alone a good contribution, now have it published too Wink

After copying I cleaned up the code on my system as the article has many keywords merged, like “innerjoin” instead of “inner join” and “casewhen” instead of “case when”. I understand all that is happening, except for one thing…the ordered part of the output and more especially…why.

I understand how the numbers come to be, but am uncertain what good the order is in an in-line function. To my knowledge no order assumption can be made by the consuming code, unless something is at work here I don’t know or realize yet.

As for performance I would have to run many tests, especially how it interacts in more complex operations that usually involve a traditional tally table for speedup.


I welcome the testing you will do and would be interested in the results. As to the ordered part of the output, I think it has to do with the following code fragment:


row_number() over (order by a1.N)



Something I have noticed when working with the windowing functions in queries using them, if the query itself does not have an ORDER BY, the ORDER BY in the windowing function orders the output.

I'd be interested if anyone else has seen this behaviour as well.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
MrAkki
MrAkki
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 490
Nice article. :-)

Another way would be:


DECLARE @Start BIGINT, @Step BIGINT, @Max BIGINT
SELECT @Start = 1, @Step = 10 , @Max = 10000000


SELECT (Number * @Step + @Start)
FROM (
SELECT TOP (@Max/@Step)
ROW_NUMBER() OVER (ORDER BY c.[object_id]) - 1 AS Number
FROM sys.columns AS c WITH (NOLOCK)
CROSS JOIN sys.columns AS c2 WITH (NOLOCK)
CROSS JOIN sys.columns AS c3 WITH (NOLOCK)
CROSS JOIN sys.columns AS c4 WITH (NOLOCK)
CROSS JOIN sys.columns AS c5 WITH (NOLOCK)
) a
WHERE (Number * @Step + @Start) <= @Max



It comes close to the ufn_Tally2 Function just a little more reads...

Regards,

Thorsten
peter-757102
peter-757102
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1079 Visits: 2559
We really need a mode of SQL server where al output order is randomized if no ordering is in effect (explicit or otherwise). This would take away the guessing out of some SQL constructs. So if it can go wrong, it will go wrong and right on your first test run.

Personally I would never rely on implicit ordering, we seen how that effect can change as the LOP changes due to available cores (remember the partitioned version of running totals?).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search