Создание CLR функций/процедур с помощью технологии SQLCLR

Функционал SQL Server очень широк и для большинства операций с даннными нам хватает возможностей T-SQL. Но иногда необходимо выполнить над данными в базе данных такие операции, для которых не хватает функционала стандартных средств SQL Server. Классический пример такой задачи - регулярные выражения. Одним из вариантов их решения является использование технологии SQL CLR – возможность создавать CLR функции/процедуры
, путем подключения самостоятельно написанных библиотек, c реализованным нами функционалом, например с помощью VS на C# (это является официальным средством расширения функционала SQL Server :)). Кроме того ряд задач, SQL CLR может выполнить быстрее чем T-SQL, например какие-то сложные вычисления. В этом посте я покажу как внедрить в SQL Server поддержку регулярных выражений про помощи SQL CLR.

Итак, что мы имеем:

- наша задача – реализовать логику проверки соответствия сложному регулярному выражению текстового поля таблицы в базе данных в VS и представить результат в виде dll, которую мы потом подключим в SQL Server и будем использовать;

- исходные инструменты – MS Visual Studio 2010, MS SQL Server 2011 Denali.

План действий:

1.       Создает в VS новый пустой проект (назовем его Helper), в котором мы и реализуем необходимую логику, ниже на рисунке показан один из возможных вариантов реализации.

На этом этапе необходимо обратить внимание на следующие вещи:

- для работы нам понадобится подключить библиотеки выделенные на рисунке красным цветом;

- мы можем навесить различные атрибуты с параметрами на наш метод, в нашем примере мы использовали атрибут SqlFunction, в котором задали свойству IsDeterministic значение true, т.е. показали что наша Sql функция будет детерминирована – выдает одни и теже результаты при неизменных входных значениях. Также это свойство можно использовать для индексации результата функции. На рисунке этот участок кода выделен зеленым цветом;

- тип входных и выходных значений нужно определить используя формат Sql + тип. На рисунке этот участок кода подчеркнут синими линиями;

- наш класс и соответственно метод должны быть статическими.

Билдим наш проект, получаем библиотеку и переходим к следующему этапу.

Примечание: в SQL Server 2008 необходимо было создавать библиотеку на основе .NET Framework 2.0, в SQL Server 2011 Denali можно уже можно использовать .NET Framework версии 4.0.

2.       Теперь нам необходимо добавить нашу библиотеку в базу данных. Для этого можно воспользоваться либо скриптом:

где CLRFUNCTION имя нашей сборки, либо с помощью контекстного меню:

                   В результате у нас должно получиться что-то вроде этого:

                   После этого переходим к следующему заключительному этапу.

3.       Теперь нужно собственно создать CLR функцию. Для этого можно воспользоваться следующим скриптом.

Как видно из скрипта, сначала мы задаем имя нашей функции и входящие параметры, тип возвращаемого значения и собственно источник в формате [имя сборки].[имя класса].[имя метода]. Теперь мы можем пользоваться нашей CLR функцией как обычной Sql – левской., например:

Послесловие.

            Для того чтобы пользоваться CLR функциями необходимо проверить чтобы глобальный параметр вашего сервера «clr enabled» имел значение равное единице  (по умолчанию он равен нулю). Посмотреть или изменить параметр можно с помощью sp_configure.

Ссылки по теме:

1. Технология SQLCLR - http://msdn.microsoft.com/en-us/library/ms345136(v=sql.90).aspx
2. Microsoft.SqlServer.Server - пространство имен - http://msdn.microsoft.com/ru-ru/library/microsoft.sqlserver.server.aspx
3. Создание CLR функций - http://msdn.microsoft.com/en-us/library/ms189876.aspx