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

Lynn Pettis (9/22/2009)

---

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.

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.

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.

        row_number() over (order by a1.N)