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.