This morning I decided to delve into the wonders of managed code running in SQL 2005. To me it was obvious that the best candidate for this technology was a function that split a comma separated list of values and returned a table. I've used a variety of different approaches to solve this problem, ranging from a TSQL UDF that split the values to a CHARINDEX and LIKE trick. I was really hoping that a managed UDF would be orders of magnitude faster than a TSQL implementation of this sort.
So I created a SQL Server Project in Visual Studio 2005 and created the following class:
using System;
using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions
{
[SqlFunction(FillRowMethodName = "FillVarcharRow", TableDefinition = "value nvarchar(200)")]
public static IEnumerable GetCsvVarcharTable(string commaSeparatedList)
{
return commaSeparatedList.Split(new char[] { ',' });
}
public static void FillVarcharRow(object row, out string value)
{
value = (string)row;
}
}
After deploying it I tried to execute the UDF only to find out that CLR extensions are disabled by defualt. The following script got me back on my way:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
I plan on taking a closer look at the performance implications of using the managed UDF and I will update the post as more information becomes available.