Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

The Dynamic Tally or Numbers Table Expand / Collapse
Author
Message
Posted Tuesday, September 22, 2009 8:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:56 AM
Points: 2,699, Visits: 1,144
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
Post #791838
Posted Tuesday, September 22, 2009 8:39 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
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.



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)
Post #791845
Posted Tuesday, September 22, 2009 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:56 AM
Points: 2,699, Visits: 1,144
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 , 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
Post #791848
Posted Tuesday, September 22, 2009 8:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
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.



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)
Post #791854
Posted Tuesday, September 22, 2009 8:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 8:32 AM
Points: 163, 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.
Post #791856
Posted Tuesday, September 22, 2009 8:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 24, 2014 2:04 PM
Points: 148, Visits: 93
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"
Post #791859
Posted Tuesday, September 22, 2009 8:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:21 AM
Points: 334, Visits: 2,273
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 ;)

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.
Post #791863
Posted Tuesday, September 22, 2009 9:22 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 20,808, Visits: 32,746
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 ;)

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.



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)
Post #791906
Posted Tuesday, September 22, 2009 9:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:25 AM
Points: 256, Visits: 454
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
Post #791933
Posted Tuesday, September 22, 2009 9:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 24, 2014 6:21 AM
Points: 334, Visits: 2,273
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?).

Post #791936
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse