jasona.work (3/15/2016)
Is this the end?Will Jeff Moden be able to stop work on his string splitter??
Will Batman be able to get rid of the bomb before it blows up?!?!
Tune in next week!
https://www.brentozar.com/archive/2016/03/splitting-strings-sql-server-2016-rescue/[/url]
😎
I ran a test comparing Jeff's DelimitedSplit8K (DS8K) to the STRING_SPLIT (SS) function. Ran against 10k rows of random delimited data. Also tested with putting output into a temp table, and using a @Bitbucket VARCHAR(MAX) variable. Average results over 5 runs:
DS8K -> @Bitbucket: 49138ms
DS8K -> TempTable: 51054ms
SS -> @Bitbucket: 4471ms
SS -> TempTable: 8871
If using 10k rows of identical data, DS8K was faster than SS.
To answer some of the questions I've seen on The Thread:
STRING_SPLIT('', ',') returns a single row of an empty string.
STRING_SPLIT(NULL, ',') returns no rows. To get a row with a NULL result value, use this function in an OUTER APPLY.
Using Jeff's test of a splitter against things that some splitters choke on, it passed all (except the NULL).
According to the docs, it returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise returns varchar. The length of the return type is the same as the length of the string argument.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes