December 11, 2018 at 1:05 am
Jeff Moden - Monday, December 10, 2018 11:58 PMLot'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?
December 11, 2018 at 3:02 am
Jeff Moden - Monday, December 10, 2018 9:40 AMEirikur Eiriksson - Monday, December 10, 2018 9:09 AMAB'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"
😎
December 11, 2018 at 8:32 am
Jason A. Long - Tuesday, December 11, 2018 1:05 AMJeff Moden - Monday, December 10, 2018 11:58 PMLot'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
December 11, 2018 at 10:24 am
Jeff Moden - Tuesday, December 11, 2018 8:32 AMORDER BY (SELECT NULL)
Find ORDER BY @@VERSION simpler and somewhat more efficient 😉
😎
December 26, 2018 at 6:10 pm
Eirikur Eiriksson - Monday, December 10, 2018 9:05 AMThanks Steve, don't think there are too many (poor buggers) on 2008 or earlier,
😎
You won't believe it.
_____________
Code for TallyGenerator
December 27, 2018 at 9:04 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply