New Function String_Split in SQL Server 2016

SQL Server 2016 has many features which makes developer life bit easy to do various task. Earlier to split a string developer use to write a user defined function using temp tables and complex logic. In almost every database I see a user defined function exists which does string split work. The project for which I work we pass comma delimiter parameters values to SSRS report stored procedure where we use string split function to get all individual value and store them in a temp table to use as filter criteria in the query.

In SQL Server 2016 a new table valued function String_Split is introduced which will make this task easy. We need not required to write our own user defined function to split a string.


STRING_SPLIT (string, separator)

Where String is expression of character type CHAR,NCHAR, VARCHAR and NVARCHAR and Separator is an expression of character type CHAR(1), VARCHAR(1), NVARCHAR(1) and NCHAR(1).

It returns a single column table. The name of the column is value. It returns nvarchar or nchar if any of the input parameter is of type nvarchar or nchar else it returns of  varchar.



As far as performance is concern it performs fairly better than the user defined function. I have tested performance with various user defined function readily available in internet and found String_split performs better than any other user defined function. If in your environment is running on SQL Server 2016 and you are using any user defined function for string split then I would recommend to use this function.


About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published.