I have done some fairly extensive tests on the string splitting options and the JSON split methods and the STRING_SPLIT functions are by far the best performers if one is on 2016 or later, by far!
Encapsulating the output in a CTE with a ROW_NUMBER has proven to be accurate and consistent when it comes to the item's ordinals, hence my recommendation.
😎Hopefully I will find the time to do a write-up soon
Understood but MS has not made a guarantee as to order even when using the likes of ROW_NUMBER. I understand how the CLR behind the scenes may have been written and if was written as such, will reliably return the correct order with ROW_NUMBER if that's the only sort. I don't have an example right now but I have seen multiple sorts resulting from multiple ROW_NUMBERs throw things out of whack when using an operand such as (SELECT NULL) for the ORDER BY part of ROW_NUMBER.
Unlike a thing like the Quirky Update, it is difficult to build in a check to see if the sort order is the same as the original string without adding a substantial amount of overhead in the form of rebuilding the string from the returned elements to do a compare to the original.
Use with caution.
Until MS finishes the function by adding ordinal and guarantees order, STRING_SPLIT has limited uses for me. There are some cases where order isn't important, but when dealing with things like IP addresses, paths and a lot of other types of data, it's important. I'll continue to use DS8K (original and improved) until the function is finished.
I read about the rumor in the comments on Brent's site and I've seen the MS Connect item at https://connect.microsoft.com/SQLServer/Feedback/Details/2433171. It's only 15 months old and the vote is 62 Yes to 0 No. We'll see if MS decides to act on it. Personally, I really hope they do.
Between it missing the element ordinal and the fact that it doesn't return a NULL when passed a NULL, I have little use for it.
Good points as always Jeff, it would though take a fairly twisted programming not to return the items in the correct order, slightly sinister if that's the case.
😎
The JSON method is almost as fast or in some cases equal but then again, haven't seen anything on the order of items returned.