An Overview of STRING_SPLIT()

Question

An Overview of STRING_SPLIT()

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

Jeff Moden - Monday, December 10, 2018 11:58 PM
Lot's of people have been doing that trick with RowNumber. Thinking about the code behind the scenes, it should be guaranteed but MS refuses to state that or even imply that. It's really a shame they didn't take the time to return the element ordinal.

I 100% agree with that...The lack of element position number boggles the mind...
That said, at this point, I'm just curious to see just how robust the ROW_NUMBER "hack" can be.
I'm inclined to put it into the same category as the Quirky update... It certainly isn't guaranteed and yet, if certain rules are followed, it does produce consistent, predictable results... and it has continued to do so for at least the last decade now.
So yes, to have a guaranteed order you MUST use an ORDER BY clause... Period... Full stop...
I'm not challenging that at all... I'm simply suggesting that there may be a similar set of rules that, if adhered to, will cause STRING_SPLIT to produce consistent, predictable results as well.
What are the conditions that will cause the ROW_NUMBER to not follow the input string order?

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 2

Jeff Moden - Monday, December 10, 2018 9:40 AM
Eirikur Eiriksson - Monday, December 10, 2018 9:09 AM
AB's article isn't bad but it's incomplete and hasn't been appropriately updated.
😎
One thing AB missed entirely is the efficiency of fixed width parameter strings, blows the chops of any other methods by a considerable margin (must finish that article)
Sorry but have to disagree there. I did try to coach Aaron publically and privately and, instead, he simply stated he wasn't going to do any additional testing and closed the article to any further comment. Anyone reading it can easily come to the same wrong conclusion that he did because of the "Holy Grail" nature (an article that contains code to test functionality and performance as well as having charts that "prove" the findings, which are horribly wrong in that case) of his article and people end up drinking from the wrong cup and "dying" when the deploy such poor methods to a production environment. even worse, his article didn't take into account any special characters and the XML code he published causes a failure there..

Didn't come out as intended, guess it's my broken English, must have misspelled "sarcasm"
😎

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Jason A. Long - Tuesday, December 11, 2018 1:05 AM
Jeff Moden - Monday, December 10, 2018 11:58 PM
Lot's of people have been doing that trick with RowNumber. Thinking about the code behind the scenes, it should be guaranteed but MS refuses to state that or even imply that. It's really a shame they didn't take the time to return the element ordinal.
I 100% agree with that...The lack of element position number boggles the mind...
That said, at this point, I'm just curious to see just how robust the ROW_NUMBER "hack" can be.
I'm inclined to put it into the same category as the Quirky update... It certainly isn't guaranteed and yet, if certain rules are followed, it does produce consistent, predictable results... and it has continued to do so for at least the last decade now.
So yes, to have a guaranteed order you MUST use an ORDER BY clause... Period... Full stop...
I'm not challenging that at all... I'm simply suggesting that there may be a similar set of rules that, if adhered to, will cause STRING_SPLIT to produce consistent, predictable results as well.
What are the conditions that will cause the ROW_NUMBER to not follow the input string order?

Supposedly, the use of another sort of one form or another. For example, another windowing function with either a PARTITION BY or and ORDER BY. I can't see that happening except in some incredibly weird edge case where the ORDER BY (SELECT NULL) is somehow over-ridden but it's much more difficult to prove that something will never happen and so I can't say it won't happen.

It would really be nice if the people that wrote the code behind the scenes would make the evaluation at the code level and say that it will return the elements in the order they were provided ... or say that it's actually not guaranteed. Of course, the former would require them to do some work and the latter will not so I don't expect any such help from MS.

I know we all know this but I'll say it again... I'm totally gob-smacked that they released the function without returning the ordinal position of elements. Really some short sighted development there.

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 4

Jeff Moden - Tuesday, December 11, 2018 8:32 AM
ORDER BY (SELECT NULL)

Find ORDER BY @@VERSION simpler and somewhat more efficient 😉
😎

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Eirikur Eiriksson - Monday, December 10, 2018 9:05 AM
Thanks Steve, don't think there are too many (poor buggers) on 2008 or earlier,
😎

You won't believe it.

_____________
Code for TallyGenerator

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 6

There are lots of people on 2005/2008. In fact, one of the surveys I saw a few months back noted that 2008 is one of the most installed versions. Lots of legacy code there, especially as people upgraded from 2005 to that point and often stopped.