Split function in SQL Server – PERFORMANCE BEST PRACTISE

So far there is no inbuilt split function available in SQL Server to split a delimited string into an array or table format. In my last article I talked about, splitting a delimited string into columns and rows. In this blog post I will create four different split functions to split a delimited string into tabular format or array and then will compare the performance between them. Let’s see which one wins the race!

SPLIT FUNCTION: Using recursive CTE

To know more about recursive CTE in SQL Server please refer to this link

SPLIT FUNCTION: Using while loop

SPLIT FUNCTION: Using XML query

SPLIT FUNCTION: Using CLR based object
Here is the CLR code written in C# by Adam Machanic to split a delimited string. I have compiled the piece of code using visual studio. You can can directly download the dll from here. Now the next step is to create an assembly in SQL Server for the same and then create a table valued assembly function.

We have created 4 split functions, and all of them does the same job, splitting a delimited string into array. Enable the execution plan (CTRL + M) to see all of them in action.

split-function-in-sql-server

As you can see, I executed all the functions except “ufn_splitString_XML” in a batch to split a same string to get a handful comparison on performance. The reason I have excluded the xml function is, it does not show the shared resource in execution plan. The above screen capture tells that the CLR one performed well than other two functions. The reason CLR performed well because, loop and recursion are little slower in SQL Server compared to c#. XML one also does a pretty good job splitting a delimited string but the only disadvantage is, it can not handle all types of special characters in the string.

My preferences  would be the CLR one (ufn_splitString_CLR), if in case you do not want to go with CLR then the XML one (ufn_splitString_XML) would be a reasonable choice only if you know the input strings are safe and does not contain much special characters. However between “ufn_splitString_CTE” and “ufn_splitString_LOOP“, both performs almost same but I think in a long run recursive one will perform good than the loop.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “Split function in SQL Server – PERFORMANCE BEST PRACTISE

  • December 28, 2014 at 6:08 pm
    Permalink

    The percentages shown in the plan are just estimates, and quite often have nothing to do with the real performance. There’s quite many blog posts that explain this same thing and have done actual performance analysis for different solutions, for example this by Jeff Moden: http://www.sqlservercentral.com/articles/Tally+Table/72993/

    Reply
    • December 28, 2014 at 8:57 pm
      Permalink

      Thank you Toni for the reply. I completely agree with you but I have personally tested other solutions with the CLR one and found CLR is doing a pretty good job splitting delimited string into an array.

      Reply

Leave a Reply

%d bloggers like this: