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.

    Also popular now: