Ronen Ariely has mixed feelings on updates to the STRING_SPLIT function:
The main issue with this function, is that it returns a SET of rows with no specific order.
As you must know by now, a TABLE is a SET of rows (Rowstore table which is the more common in SQL Server) or columns (Columnstore table). The rows in the table are not stored in specific order (even if using clustered index, the rows can physically be stored in different locations on the disk, not necessarily maintained continuously one after the other. In addition, the server might read the rows in parallel and not necessarily in the order of the index. As a result, The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
And this is the main issue with the STRING_SPLIT… until today
Read on to see how this update makes STRING_SPLIT() much better, and also how it could be even better still.