Site
Web

You are here:

Blog Archive:

* Can be used in order to search for older blogs Entries

Search in blogs

KeywordsPhrase

Blog Categories:

* Can be used in order to search for blogs Entries by Categories

Blog Tags:

* Can be used in order to search for blogs by keywords

TNWikiSummit

Microsoft® Community Contributor

Read this before you use the blog!

New! Azure SQL supports STRING_SPLIT with order guaranteed - the awesome, the bad, and the expected

נוב5

Written by: ronen ariely
05/11/2021 00:00

Introduction

Microsoft Ignite is behind us and it is time to start notice the enhancements in the Transact-SQL which we were promised, and today I will show you one of these which just updated today!

I assume that you all familiar with the table function STRING_SPLIT, which was added in SQL Server 2016 and about the same time it was added to the Azure SQL Database.

DECLARE @String NVARCHAR(MAX)

SELECT @String = 'Ronen, Ariely, Demo, New, string_split'

SELECT * from STRING_SPLIT(@String, ',')

GO

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 😀

New of today!

Microsoft improved the function STRING_SPLIT and added a new optional parameter name: enable_ordinal

By default, if you will not add this parameter, then everything will behave mike before - STRING_SPLIT will return a single-column table, which mean that we have full backsword compatibility.

But as of today in the Azure SQL Database, if the enable_ordinal parameter is passed with the value 1, then a second column named ordinal is returned. The column ordinal will include value datatype bigint, according to each substring's position in the input string !

THIS IS AWESOME and highly important improvement, which people asked from about the time they saw the function STRING_SPLIT.

Here is a simple example of using the new improved function:

DECLARE @String NVARCHAR(MAX)

SELECT @String = 'Ronen, Ariely, Demo, New, string_split'

SELECT * from STRING_SPLIT(@String, ',') -- old version

SELECT * from STRING_SPLIT(@String, ',', 0) -- using new parameter with value 0 is like old version

SELECT * from STRING_SPLIT(@String, ',', 1) -- Using the new improved function!

GO

Well... this was the awesome news.... next, here are my insights for enhancement of the feature even more...

Ronen Ariely Insights/Thought

There is no question about it, this is an awesome improvement but one is a bit disappointing for me in away.

My request during many (public) discussions, is not just yes/no and get the original order. Instead, I asked for third parameter which has much more flexibility and I am 100% that it was not taken more time to implement the new parameter as such:

We need an option to get the result in a more flexible option according to the order that we select.

Yes, the default should be to return the result SET in the order according to each substring's position in the input string, but give us option to get it sorted in other option as well.

What about reverse order (DESC)?

What about order by the string of the values after the split?

The STRING_SPLIT function is already scanning all the values. Moreover, now, it even make the sorting, so it can sort it differently while doing so inside the CLR code.

Today if we need to SET in a different order, then we have to sort (order by) the result set AFTER it came back from the function to the server engine, and this cost a lot.

There is no reason to sort the data again after the splitting was done.

In my opinion, the parameter could include multiple build-in functions, a bit like we have in Dynamic Data Masking when one parameter can be set for multiple different options.

The minimum which I asked for is to have the option to use the value 0-4

value 0: No order like today
value 1: Sort according to each substring's position in the input string, like the new improved option added today
value 2: sort in reverse order
value 3: sort by the string value
value 4: sort by the string value in reverse order

DECLARE @String NVARCHAR(MAX)

SELECT @String = '3, 7, 3, 0, 1'

SELECT * from STRING_SPLIT(@String, ',', 0)

SELECT * from STRING_SPLIT(@String, ',', 1)

SELECT * from STRING_SPLIT(@String, ',', 2)

SELECT * from STRING_SPLIT(@String, ',', 3)

SELECT * from STRING_SPLIT(@String, ',', 4)

GO

So this is like a minimum (at least option 2), but what I really want to see and asked for, is even more flexibility...

In most cases the string which we use as input for the split action represent a SET of entities. For example, it can be a SET of use ID (which mean a numeric entity), It might be a SET of dates and/or time, or a SET of strings, and so on...

The return values should be CONVERTTED to the right type and sorted according to the type and a free function we can use.

Demo 01 : requested sorting by type

DECLARE @String NVARCHAR(MAX)

SELECT @String = 'a, e, b, d, c'

SELECT * from STRING_SPLIT(@String, ',', 'string')

SELECT * from STRING_SPLIT(@String, ',', 'string desc')

GO

DECLARE @String NVARCHAR(MAX)

SELECT @String = '3, 7, 3, 0, 1'

SELECT * from STRING_SPLIT(@String, ',', 'INT')

SELECT * from STRING_SPLIT(@String, ',', 'INT desc')

GO

DECLARE @String NVARCHAR(MAX)

SELECT @String = '2025-02-27, 2024-02-27, 2023-02-27, 2022-02-27, 1'

SELECT * from STRING_SPLIT(@String, ',', 'DATE')

SELECT * from STRING_SPLIT(@String, ',', 'DATE desc')

GO -- Note! remember these dates as this is my next Birthdays :-)

Demo 02: requested sorting by free function

DECLARE @String NVARCHAR(MAX)

SELECT @String = 'a, e, b, d, c'

SELECT * from STRING_SPLIT(@String, ',', 'CONVERT(INT,[value])') -- you can use any function on the column [value]

SELECT * from STRING_SPLIT(@String, ',', 'SUBSTRING([value], 2, 3)') -- you can use any function on the column [value]

GO

AND THIS COULD BE THE OPTIMAL SPLITING FUNCTION 😀

What next?

Note! I have no idea since I have no internal information yet under NDA, which is I hate it 😕. With that said, it also has an advantage... I can speak freely and guess and publish my guess as it is just a guess like anyone of you can do.

I think it's pretty clear that in the upcoming version of SQL Server 2022, we'll see this improvement (or maybe my suggestion or anything between?!?). This is a huge improvement!

Closure

I would like to take this opportunity to thank the Microsoft Data Platform team for being available and listening to community requests. Improving the function is definitely a good example of this. Thanks.

And to your my readers... You are more than welcome to send me your insights in Facebook or linkedin.

Hope this was interesting and useful

Some important/nice discussion about this post

copied here screenshot of selected threads

For security reason and laziness, I removed the option to register to the site after I got more than 500k registers, which most are fake. Managing the user took too much in addition to fact that this is an old version of DotNetNuke 5.x and I did not waned to spend time on security. Therefore, if you have any comment and insights please feel free to send me directly as I mentioned above or simply join one one of the discussions on other social media networks.