
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.
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.
As a result, the table #tmpIDs will contain
Create a SplitString.cs file with the following contents:
Compile the module:
The output is SplitString.dll.
Now, you need to enable the use of the CLR in SQL Server.
Everything, you can connect the module.
And create a custom function.
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 .
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.
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.