Using SQLCLR to Increase Productivity

Starting with MS SQL Server 2005, a very powerful SQL CLR technology was added to database developers .

This technology allows you to extend the functionality of SQL server using .NET languages, such as C # or VB.NET.

Using the SQL CLR, you can create your own stored procedures, triggers, user types and functions, as well as aggregates, written in high-performance languages. This allows you to seriously improve performance and expand the functionality of the server to unimaginable boundaries.

Consider a simple example: we write a user-defined function for cutting a line by a separator using SQL syntax and SQL CLR based on C # and compare the results.

Custom function returning a table

CREATE FUNCTION SplitString (@text NVARCHAR(max), @delimiter nchar(1))
RETURNS @Tbl TABLE (part nvarchar(max), ID_ORDER integer) AS
BEGIN
  declare @index integer
  declare @part  nvarchar(max)
  declare @i   integer
  set @index = -1
  set @i=1
  while (LEN(@text) > 0) begin
    set @index = CHARINDEX(@delimiter, @text)
    if (@index = 0) AND (LEN(@text) > 0) BEGIN
      set @part = @text
      set @text = ''
    end else if (@index > 1) begin
      set @part = LEFT(@text, @index - 1)
      set @text = RIGHT(@text, (LEN(@text) - @index))
    end else begin
      set @text = RIGHT(@text, (LEN(@text) - @index))
    end
    insert into @Tbl(part, ID_ORDER) values(@part, @i)
    set @i=@i+1
  end
  RETURN
END
go


This function cuts the input string using a delimiter and returns a table. It is very convenient to use such a function, for example, for quickly filling a temporary table with records.

select part into #tmpIDs from SplitString('11,22,33,44', ',')

As a result, the table #tmpIDs will contain

11
22
33
44

CLR module written in C #

Create a SplitString.cs file with the following contents:

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class UserDefinedFunctions {
  [SqlFunction(FillRowMethodName = "SplitStringFillRow", TableDefinition = "part NVARCHAR(MAX), ID_ORDER INT")]

  static public IEnumerator SplitString(SqlString text, char[] delimiter)
  {
    if(text.IsNull) yield break;

    int valueIndex = 1;
    foreach(string s in text.Value.Split(delimiter, StringSplitOptions.RemoveEmptyEntries)) {
      yield return new KeyValuePair(valueIndex++, s.Trim());
    }
  }

  static public void SplitStringFillRow(object oKeyValuePair, out SqlString value, out SqlInt32 valueIndex)
  {
    KeyValuePair keyValuePair = (KeyValuePair) oKeyValuePair;
    
    valueIndex = keyValuePair.Key;
    value = keyValuePair.Value;
  }
}


Compile the module:

%SYSTEMROOT%\Microsoft.NET\Framework\v2.0.50727\csc.exe /target:library c:\SplitString.cs

The output is SplitString.dll.

Now, you need to enable the use of the CLR in SQL Server.

sp_configure 'clr enabled', 1
go
reconfigure
go

Everything, you can connect the module.

CREATE ASSEMBLY CLRFunctions FROM 'C:\SplitString.dll'
go

And create a custom function.

CREATE FUNCTION [dbo].SplitStringCLR(@text [nvarchar](max), @delimiter [nchar](1))
RETURNS TABLE (
part nvarchar(max),
ID_ODER int
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME CLRFunctions.UserDefinedFunctions.SplitString


More on the CLR

1. The assembly is uploaded to the server and stored there. Functions that reference the assembly are already stored in the database. Therefore, it is necessary that the assembly is loaded on the server where the database is being transferred.

2. When creating an assembly, if necessary, the PERMISSION_SET argument is specified, which determines the permissions for the assembly. I advise you to look at MSDN . In short: SAFE - allows you to work only with the database; EXTERNAL_ACCESS - allows you to work with other servers, the file system and network resources; UNSAFE - anything, including WinAPI.

3. There are features when debugging which ones are specified in MSDN .

results

To compare the speed of a regular SplitString and SplitStringCLR, I called these functions 1000 times with an input string consisting of 100 comma-separated numbers.

The average run time for SplitString was 6.152 ms, and for SplitStringCLR 1.936 ms.

The difference is more than 3 times.

I hope this will be useful to someone.