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.
Syntax:
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.
