In this tutorial I will demonstrate how to create a CLR User Defined Function (UDF) in Visual Studio, and then integrate it into SQL Server.
Why do I need a CLR User Defined Function?
Many SQL Server professionals have recently proved that performing certain operations in SQL using a CLR function rather than the built-in SQL functions provides massive performance gains. One such example that caught my attenion was Adam Machanic's String Splitter. This marvelous piece of code takes a comma-separated list of numbers and puts them into a table.
In my personal development, splitting strings was never an issue until recently we won a large contract for a customer that had hundreds of thousands of rows of data (in string format) that needed inserting into a SQL table. Using our existing TSQL-only method, our server crawled to nearly stopping point trying to process this data...
SQL-only Functions can be Slow
The following T-SQL code creates a function which in turn creates a table variable, and inserts 100,001 rows of data by splitting a string into numeric values:
CREATE FUNCTION myfn_StringSplitter
(
@separator char(1),
@IdString nvarchar(max)
)
RETURNS @tmp TABLE (ID int)
AS
BEGIN
DECLARE @separator_position int
DECLARE @array_value nvarchar(max)
WHILE patindex('%' + @separator + '%' , @IdString) <> 0
BEGIN
select @separator_position = patindex('%' + @separator + '%' , @IdString)
select @array_value = left(@IdString, @separator_position - 1)
INSERT INTO @tmp (ID) VALUES (@array_value)
SELECT @IdString = stuff(@IdString, 1, @separator_position, '')
END
RETURN
END
GO
SELECT * FROM dbo.myfn_StringSplitter(',', '1101, 3645, 4894, 9182, 3617, 2427 ...... removed for brevity ...... 5213, 9197, 4758, 4728, 9665, 9120, 991, 2126,')
Running the query on my development machine took 278.61 seconds:
How to Create a CLR User Defined Function in Visual Studio
I will now demonstrate how to build a CLR function in Visual Studio that can achieve the above in record time.
Start a new project of type SQL CLR Database Project:
Delete the files that Visual Studio adds automatically, and then add a new item, of type User-Defined Function:
Now paste this code over the top of the existing code (or write your own function obviously). Credit here again goes to Adam Machanic, of which I slightly modified his excellent version to allow NULL values to be created from empty strings:
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Runtime.InteropServices
Public Class MyStringSplitter
Private Class DoSplit
Implements IEnumerator
Private lastPos As Integer
Private nextPos As Integer
Private theString As String
Private delimiter As Char
Public Sub New(s As String, d As Char)
Me.theString = s
Me.delimiter = d
Me.lastPos = -1
Me.nextPos = -1
End Sub
Public ReadOnly Property Current() As Object Implements IEnumerator.Current
Get
Return theString.Substring(lastPos, nextPos - lastPos).Trim()
End Get
End Property
Public Function MoveNext() As Boolean Implements IEnumerator.MoveNext
If nextPos >= theString.Length Then
Return False
Else
lastPos = nextPos + 1
If lastPos = theString.Length Then
Return False
End If
nextPos = theString.IndexOf(delimiter, lastPos)
If nextPos = -1 Then
nextPos = theString.Length
End If
Return True
End If
End Function
Public Sub Reset() Implements IEnumerator.Reset
Me.lastPos = -1
Me.nextPos = -1
End Sub
End Class
<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRow", TableDefinition:="IdVal INT")> _
Public Shared Function SplitToIntegers(instr As SqlString, <SqlFacet(IsFixedLength:=True, MaxSize:=1)> delimiter As SqlChars) As IEnumerator
Return New DoSplit(instr.Value, Convert.ToChar(delimiter.Value))
End Function
Public Shared Sub FillRow(obj As Object, <Out()> ByRef IdVal As SqlInt32)
If obj.ToString = String.Empty Then
IdVal = SqlTypes.SqlInt32.Null
Else
IdVal = New SqlInt32(obj)
End If
End Sub
End Class
Now build the function into a DLL file:
Make a note of where the DLL has been created, for example:
C:\Users\Dave\Documents\visual studio 2010\Projects\MyStringSplitter\bin\Debug\MyStringSplitter.dll
Integrating a CLR UDF into SQL Server
Configure SQL to accept CLR UDF's
Fire up SQL Management Studio and run this code which will allow us to add new CLR functions into our database:
USE MyDatabase
GO
sp_configure 'clr enabled', 1
GO
-- Output: Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
Now run the RECONFIGURE command as requested by SQL:
RECONFIGURE
GO
-- Command(s) completed successfully.
Load our .NET CLR UDF into the SQL Database
Load the DLL file into the SQL server database (the DLL can be safely deleted from the file system once this step is complete):
CREATE ASSEMBLY e4Splitter
FROM 'C:\Users\Dave\Documents\visual studio 2010\Projects\MyStringSplitter\bin\Debug\MyStringSplitter.dll'
WITH PERMISSION_SET = SAFE
GO
-- Output: Command(s) completed successfully.
Note, if at this point if you get this error...
CREATE ASSEMBLY for assembly <Assembly Name> failed because the assembly is built for an unsupported version of the Common Language Runtime
...then you need to change the Target Framework to a different option in the Advanced Compiler Settings dialog (I used 3.5)
Create the SQL Function from our .Net CLR UDF
CREATE FUNCTION dbo.myfn_StringSplitterCLR
(
@Ids nvarchar(max),
@separator nchar(1)
)
RETURNS TABLE
(
IdVal int NULL
)
AS EXTERNAL NAME [SplitterAssembly].[MyStringSplitter.MyStringSplitter].[SplitToIntegers]
GO
-- Output: Command(s) completed successfully.
If you receive this error at this point:
Could not find Type '<TypeName>' in assembly 'MyStringSplitter'
Check out this article: T-SQL error: Could not find type <TypeName> in assembly <AssemblyName>
Try out the new CLR UDF
Now let's test out the new function, again using 100,001 values:
SELECT * FROM myfn_StringSplitterCLR(',','8952, 5804, 9027, 5521, 5379, ..... removed for brevity...., 4939, 2900, 8507, 1211, 4366, 9743, 4612, 9035,')
Massive Performance Gain!
This time the query only took 1.926 seconds to complete, which (cue bad maths) is about 145 times faster! Wow. I hope you're convinced.