Did you know that a native STRING_SPLIT function built into SQL Server was added into SQL Server 2016? As a consultant I see so much code that call out to a scalar function that are used to split out string delimited variables into a usable list. For those that use this method I suggest you look at this function. STRING_SPLIT is a table valued function that returns a single column of your string values split out by the delimiter. This is an unusual bit of T-SQL, in that compatibility level 130 or higher is required for its use (Microsoft didn’t want to induce breaking changes into existing user code). Using this method is far more efficient and can be executed without calling a scalar function.
The Syntax,
STRING_SPLIT ( string , separator)
How to use it,
- SELECT value AS 'Flavor' FROM STRING_SPLIT('Chocolate,Vanilla,Strawberry', ',');
Results
The Plan
Here is what the plan looks like. It's very straight forward and simple.
Custom Function
Now here is a home-grown version you may find in some environments. You can see its much less efficient.
- /****** Object: UserDefinedFunction [dbo].[fnSplit] Script Date: 2/11/2020 6:26:45 PM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER FUNCTION [dbo].[fnSplit](
- @sInputList VARCHAR(8000)
- , @sDelimiter VARCHAR(8000) = ','
- ) RETURNS @List TABLE (item VARCHAR(8000))
- BEGIN
- DECLARE @sItem VARCHAR(8000)
- WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
- BEGIN
- SELECT
- @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
- IF LEN(@sItem) > 0
- INSERT INTO @List SELECT @sItem
- END
- IF LEN(@sInputList) > 0
- INSERT INTO @List SELECT @sInputList
- RETURN
- END
The Results
Results are the same. But note your field name will be the same every time, with the new function you get a little more flexibility in this.
The Plan
It's a little more complicated then the straight forward one we saw above including a sequence.
There are many different ways to write a split function to make things work, however, now that SQL Server has given us one, I highly encourage you to take a look at it. When performance tuning be sure to take a look at what you've always done in your code and look for ways to improve it such as this. You can learn more and see more examples here on
docs.microsoft.com.