С началом MS SQL Server 2005 разработчики баз данных получили действительно мощный инструмент – технологию SQL CLR. Она позволяет расширять возможности сервера, используя языки .NET, например C# или VB.NET.
Благодаря SQL CLR можно писать хранимые процедуры, триггеры, пользовательские типы и функции на высокопроизводительных языках. Это значительно повышает скорость работы и открывает новые горизонты для сервера.
Давайте рассмотрим простой пример: напишем функцию разрезания строки по разделителю как обычный T-SQL, так и через SQL CLR на базе C#. Сравним результаты – и убедимся в эффективности!
Пользовательская функция, возвращающая таблицу
CREATE FUNCTION SplitString (@text NVARCHAR(max), @delimiter nchar(1))RETURNS @Tbl TABLE (part nvarchar(max), ID_ORDER integer) ASBEGIN 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 < LEN(@text)) 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 RETURNENDgo
Эта функция разрезает входную строку по разделителю и возвращает таблицу. Использовать её можно, например, для быстрого заполнения временной таблицы значениями.
select part into #tmpIDs from SplitString("11,22,33,44", ",")
В результате таблица #tmpIDs будет содержать:
Модуль 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.NETFramework\2.0.50727csc.exe /target:library c:\SplitString.cs
В результате получим SplitString.dll. Теперь разрешаем использование CLR в SQL Server.
sp_configure "clr enabled", 1goreconfigurego
И подключаем модуль:
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 CALLERAS EXTERNAL NAME CLRFunctions.UserDefinedFunctions.SplitString
Дополнительно о CLR
1. Сборка загружается на сервер и хранится там. Функции, ссылающиеся на сборку, уже находятся в базе данных, поэтому при переносе БД нужно убедиться, что сборка присутствует на новом сервере.
2. При создании сборки можно указать параметр PERMISSION_SET, который определяет доступы. На MSDN подробно описаны варианты: SAFE (только работа с базой), EXTERNAL_ACCESS (доступ к другим серверам, файловой системе и сети) и UNSAFE