С момента появления MS SQL Server 2005 разработчики получили в свои руки по-настоящему впечатляющий инструмент - технологию SQL CLR. Это решение позволяет значительно расширить возможности сервера, используя мощь .NET языков, таких как C# или VB.NET.Благодаря SQL CLR появляется возможность создавать хранимые процедуры, триггеры, пользовательские типы и функции, а также агрегаты на высокопроизводительных языках программирования. Такой подход не просто оптимизирует работу - он позволяет вывести производительность сервера на совершенно новый уровень, открывая двери для задач, которые раньше казались невыполнимыми или крайне медленными.

Давайте посмотрим на конкретный пример: напишем функцию разрезания строки по разделителю двумя способами - используя стандартный SQL синтаксис и через SQL CLR (C#) - чтобы наглядно сравнить их эффективность.

Пользовательская функция, возвращающая таблицу

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

Эта функция берет входную строку, разбивает ее по заданному разделителю и возвращает результат в виде таблицы. Такой подход невероятно удобен, например, для быстрого заполнения временных таблиц данными.

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

В результате таблица #tmpIDs будет содержать:

11 22 33 44

Модуль CLR написанный на C#

Теперь создадим файл SplitString.cs со следующим содержимым:

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<int, string>(valueIndex++, s.Trim()); } } static public void SplitStringFillRow(object oKeyValuePair, out SqlString value, out SqlInt32 valueIndex) { KeyValuePair<int, string> keyValuePair = (KeyValuePair<int, string>) oKeyValuePair; valueIndex = keyValuePair.Key; value = keyValuePair.Value; } }

Скомпилируем модуль:

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

На выходе получаем SplitString.dll. Теперь нужно разрешить использование CLR в SQL Server.

sp_configure "clr enabled", 1 go reconfigure go

Всё, можно подключать модуль.

CREATE ASSEMBLY CLRFunctions FROM "C:\SplitString.dll"

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

И создать пользовательскую функцию:

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

Дополнительно о CLR

1. Сборка загружается на сервер и хранится там. Функции, которые ссылаются на сборку уже хранятся в базе. Поэтому нужно, чтобы на сервере, куда переносится база, сборка была загружена. 2. При создании сборки, если нужно, указывается аргумент PERMISSION_SET, который определяет разрешения для сборки. Советую посмотреть MSDN. Вкратце: SAFE - разрешает работать только с базой; EXTERNAL_ACCESS - разрешает работать с другими серверами, файловой системой и сетевыми ресурсами; UNSAFE - все что угодно, включая WinAPI. 3. Есть особенности при отладке, какие именно, указано в MSDN.

Результаты

Для сравнения скорости работы обычной SplitString и SplitStringCLR я вызвал эти функции 1000 раз с входной строкой, состоящей из 100 разделенных запятой чисел. Среднее значение времени работы для SplitString получилось 6.152 мс, а для SplitStringCLR 1.936 мс. Разница более чем в 3 раза.