Splitting CSV strings in SQL 2016

Up until SQL Server 2016, we had to write our own functions to split a CSV string into a table list. This was accomplished by writing a table value user defined function.

These functions are sprinkled all over the web and for the most part two examples are given. One using casts the string as XML and then parses the nodes to return a table and the other uses a very inefficient looping operation with charindex and substring. For performance reasons I’ve always used the XML function; but, with SQL 2016 I don’t have to worry any more, or do I?

What’s new in 2016?

In 2016 there’s a new function named STRING_SPLIT. This function is used like the sys.dm_exec_sql_text funtion. To join it to a table you need to use CROSS APPLY.

This new function is very simple. It takes a string or a column as input and a delimiter. It then outputs the source as a table.

STRING_SPLIT([COLUMN_NAME], [DELIMITER])

Comparing new and old

I’m all about performance. This being said, the first thing I wanted to see is how this new function performed. I setup the following example and before you ask; Yes, I love food! If you ever get a chance to visit Austin, Texas be sure to check out the “Hey! You gonna eat or what?” food truck. They have an amazing Shiner Bock beer battered Monte Cristo that is over the top!!

Comparing the two plans together you’d think that the new function was exponentially faster. The function only has an estimated cost of 0.0004002 vs 2971.31 for the subtree of the older example. Making the new query cost 0% for the overall batch.

Looking a little deeper

When in doubt use the tools you were given. I once saw a quote saying “A fact without supporting data is just another opinion.”

The STATISTICS IO output for both queries is the same so I looked at STATISTICS TIME. Here’s what I found: