The Panjandrum Conundrum

Question

The Panjandrum Conundrum

Viewing 8 posts - 16 through 22 (of 22 total)

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

x SSC-Insane Points: 23660 More actions · Answer 1

Some say that you can trust the order of its return because of the nature of such code behind the scenes and use a ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) to create that information but that's no guarantee because we don't actually know what that underlying code is.

I get the same feeling about "quirky update".

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 2

patrickmcginnis59 10839 - Monday, July 17, 2017 10:55 AM
Some say that you can trust the order of its return because of the nature of such code behind the scenes and use a ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) to create that information but that's no guarantee because we don't actually know what that underlying code is.
I get the same feeling about "quirky update".

Once you learn to use it correctly, that feeling will pass. 😉 The new splitter doesn't provide you with any insight as to how it actually does it's thing. If we knew for sure and with the usual caveat that if you use it wrong, things can break, then the ROWNUMBER() thing might be reasonable to augment the new splitter function with.

--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.

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

Phil Factor SSC-Insane Points: 20244 More actions · Answer 3

I like quirky update, though the need for it has passed. In terms of performance it occasionally wins hands down, but it is just one of a whole palate of techniques. Long may it continue to be supported.

Best wishes,
Phil Factor

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

I think a lot more people spend time splitting strings than any of us think. Like Jeff said, there are times when order is important, such as a broken-up path. But that's not the only thing ordinal is good for - without ordinal, how are we expected to crosstab on what we've split? Until they finish the function by adding ordinal, I won't use it.

As for the MS Connect item on the built-in table of numbers, it was started by Erland Sommerskog and this past February, it celebrated its 10-year anniversary. It's not a complex request, to be sure. After all, numerous people have done extensive work in how to eek out the best performance. I've seen it mature from a physical table to a zero-read ITVF...and several variations of it. Splitting strings isn't the only thing it's good for. I believe it was Gianluca Sartori that first called it the "Swiss Army Knife of SQL" but I don't have a date. It's true. It's good for many things. I do demonstrations of using a tally table to generate a table of dates and race it against a while loop. Using the presence of rows to generate test data, gaps, date distribution frequencies - all are made easier and more efficient with a table of numbers. In fact, there have been forum threads on this site to discuss the many uses for the simple construct.

Phil, thank you for publishing this editorial. I wholeheartedly agree that the group of "sages" is out of touch with what's needed. Perhaps they need some experience to go along with all their education. Theory is fine and all, but someone (I forget who) on this site posted a very wise quote:

"In theory, theory and practice are the same. In practice, they are not."

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

DinoRS SSCrazy Points: 2683 More actions · Answer 5

I'm not sure if having Jeff on that board would really be such a good idea - at least for some of us.

Anything that tries to RBAR would throw an exception (just imagine how many would lose their jobs! 😉 )

Just kidding, on the TSQL side I wouldn't mind if the NEWID() function would actually perform well, sad to see that there are GUID generators out there which are a manifold faster then what's built in, I've seen one of these a while ago written in Assembler and judging by the code it wasn't really hard to come up with that. Considering how MS is trying to push the usage of native compiled SP (which should be / are much closer to Assembler than TSQL by syntax).

It's not the only thing that makes you wonder sometimes about MS, took them until 2019 to get rid of Silverlight in favor of HTML5 for MDS? While on the other hand according to MS Edge should be the go to browser for at least a few years already? Some organizations exclusively have to keep IE around and maintained due to %!§%*& Silverlight.

David.Poole SSC Guru Points: 76034 More actions · Answer 6

I've requested that BCP be capable of exporting in Parquet format. I think a widely used compressed format would be of great benefit and sidestep all those irritating CSV type problems.

LinkedIn Profile

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

Considering that it took almost 3 decades for them to get their fingers out of their ears on a true CSV format, I'll say "good luck with that submittal" David.

--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.

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

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 8

And how long did it take them to give us a better message than "string or binary truncation"?