I’m working on a webservice that will receive a comma separated list of ID’s and return XML and was concerned about the performance of the function I had been using in SQL to split a string into a table to join and select records.
I have been using a pretty basic iterative function that uses CHARINDEX and SUBSTRING, acceptable for small strings, but it was not going to work for this project.
Thankfully I found a CLR function in c# which has amazing performance. Credits to Adam Machanic over at SQLBlog. Find the code in his post here
http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx
To use his code, the first thing you will need to do is copy the class into a .cs file and compile it:
csc /target:exe sql_split_clr.cs
If the c sharp compiler is not in your path, add it. For SQL 2005 you want to use the framework version 2.
set path=C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727;%PATH%
CLR functions and procedures are disabled by default in SQL 2005. If you have not yet enabled it, use the following
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
You will then need to create the assembly and functions in SQL like this:
CREATE ASSEMBLY SQL_Split
FROM 'c:\clr\sql_split_clr.dll'
GO
CREATE FUNCTION string_to_list(@list nvarchar(MAX),@delim nchar(1) = N',')
RETURNS TABLE (item nvarchar(4000))
AS EXTERNAL NAME SQL_Split.UserDefinedFunctions.SplitString_Multi
GO
Now the new CLR function should be ready to use
SELECT * FROM dbo.string_to_list('a,b,c,d,e',DEFAULT)
As a final aside, what’s with having to specify the DEFAULT keyword to use the default parameter value on functions. You don’t have to with stored procedures.
