Думаю, многие из Вас слышали, о возможности писать хранимые процедуры для SQL Server 2005 на управляемых языках. Но, мне кажется, что «классический» подход, с написанием процедур на T-SQL, имеет преимущество перед SQL CLR, но это тема отдельной статьи. Однако, есть в SQL CLR, что то, что не может быть реализовано средствами T-SQL, а именно пользовательские агрегаты.

И именно о них мы и поговорим сегодня. Что такое агрегат? Это, прежде всего пользовательская функция. Но это не достаточное условие. Агрегатом я называю функцию, которая в качестве параметра обычно принимает столбец данных и может реализовывать различную логику по аккумуляции или обобщению данных в нем. Примером стандартных агрегатов могут служить функции языка T-SQL: COUNT, SUM, MAX, MIN и AVG. Так вот как оказывается, мы с вами не можем средствами T-SQL реализовывать собственные агрегаты. Думаю, многие из Вас сталкивались с различными статистическими задачами или задачами по построению отчетов, когда собственный агрегат оказался бы как нельзя к месту. Возможность реализовывать пользовательские агрегаты доступна нам через SQL CLR.

Что нам понадобится?
SQL Server 2005 (любая редакция)
Visual Studio 2005 (или выше)

Для того, что мы могли проверить работоспособность наших агрегатов, нам потребуется простенькая база данных с единственной таблицей TestTable и единственным столбцом TestColumn типа int.

По умолчанию возможность использовать SQL CLR отключена. Что бы включить ее воспользуемся специальной утилитой из поставки Microsoft SQL Server - SQL Server Surface Area Configuration. После ее запуска щелкаем на линк в самом низу окна - Surface Area Configuration for Features. Там в дереве, которое в левой части окна, находим пунктик CLR Integration и устанавливаем флажок Enable CLR integration в основной рабочей области программы. Теперь мы можем использовать CLR SQL вместе с SQL Server.

Для того, что бы работать с CLR Integration, а именно разрабатывать код и размещать его на сервере баз данных, есть несколько возможностей, но мы пойдем по пути наименьшего сопротивления  и воспользуемся специальным типом проектов в Visual Studio.

Открываем среду разработки, перемещаемся в шаблоны проектов для C# и выбираем шаблон Database. После этого нам вероятно придется указать базу данных в которую будет производиться интеграция нашего кода средствами Visual Studio.

Прежде чем двигаться дальше, нам нужно определиться с агрегатом, который мы будем вместе с Вами реализовывать. Нам нужно выбрать одновременно простой и в то же время полезный пример. Я решил, что мы напишем агрегат MID, который будет вычислять медиану по диапазону значений столбца. Что бы высчитать медиану, нам понадобится найти минимальной и максимальное значение в столбце, а потом вычислить среднее арифметическое между ними.

Если воспользоваться стандартным синтаксисом T-SQL то реализация этого будет выглядеть вот так:



После того как мы реализуем собственный агрегат MID мы сможем выполнять запросы вот так:



Уверен, к этому моменту, суть того, что мы хотим сделать должна быть уже ясна. И Вы, должны уже понять преимущества, которые это нам дает.

Для того, что бы добавить агрегат в проект, в Solution Explorer выделяем проект и из контекстного меню в пункте Add выбираем Aggregate… Файлу присваиваем имя MID.

Код, который сгенерировал мастер:



Как мы видим, наш агрегат представлен структурой с именем MID. Для реализации агрегата требуются все четыре метода, которые нам сгенерировал мастер. Стоит обратить внимание, что их реализация находится на нашей с Вами совести. Никаких интерфейсов нам не нужно реализовывать, но по «договоренности» в агрегате должны присутствовать все четыре метода: Init, Accumulate, Merge, Terminate. Разберемся с ними по порядку.

Init – используется для инициализации начальных значений и вызывается единожды при инстанцировании переменной агрегата.

Accumulate – основная рабочая лошадка, наших агрегатов. Именно в данном методе реализуется вся логика по накоплению результатов. Как мы можем заметить, данный метод принимает значение (обычно оно совпадает с типом столбца, к которому применяется агрегат). Этому методу будут передаваться значения, хранящиеся в строках обрабатываемого столбца.

Merge – что бы понять назначение данного метода, нужно немного понимать то как SQL Server обрабатывает множества данных. Очень часто эта обработка ведется сразу в нескольких потоках. Например, строки с нулевой до 500 обрабатываются одним потоком, строки с 500 по 1000 другим и.т.д. Поэтому нам нужен механизм, который бы позволил результат работы нескольких потоков собрать воедино. Именно для реализации этой задачи и предназначен метод Merge. Как мы видим он принимает параметром агрегат, следовательно, в этом методе нам придется, объединить результаты работы двух агрегатов в один.

Terminate – метод ответственный за возвращение окончательного результата работы агрегата.

С теорией разобрались. Приступим к практике.

Вычислять значение медианы мы будем для целых чисел. Поэтому метод Accumulate должен принимать целое число. Изменим значение типа параметра SqlString на SqlInt32.



Далее давайте решим, как мы будем накапливать результат. Для сохранения максимального и минимального значения в столбце объявим в нашей структуре две переменные типа SqlInt32. Вместо private int var1 напишем:



Для инициализации этих переменных воспользуемся методом Init, который примет следующий вид:



Метод Accumulate получая очередные значения, должен сравнивать их со значениями хранящимися в _min и _max и в случае необходимости обновлять их. Логика Accumulate не представляет собой ничего экстраординарного.




Что бы лучше понять назначение Accumulate я расскажу, о том как происходит выполнение работы нашим агрегатом. При выполнении запроса создается переменная нашего типа и вызывается метод Init, затем на обработку методу Accumulate поступают строки, которые удовлетворяют условию where в выражении запроса (при его отсутствии – все строки). После этого в случае необходимости вызывается методы Merge, для слияния результатов работы нескольких потоков (если таковые создавались, а следовательно, существует несколько экземпляров нашего агрегата) и только после этого вызывается метод Terminate для получения окончательного результата работы.

По сути, в нашем методе, мы ищем в столбце минимальное и максимальное значение и сохраняем их для последующего вычисления медианы в методе Terminate.

Terminate должен вычислить медиану по накопленным данным и вернуть ее из метода. Код предельно прост. Тип возвращаемого значения соответственно изменен на SqlInt32



Остается метод Merge. Нам необходимо объединить слить два агрегата воедино перед выполнением Terminate, но после выполнения Accumulate для этого нам нужно знать какие данные были накоплены в результате работы агрегата. Поэтому нам понадобятся свойства на чтение переменных _min и _max.



Воспользуемся ими для слияния накопленных данных в методе Merge:



Как видим и здесь логика не представляет особой сложности. Мы смотрим значения Min Max в полученном агрегате и если они меньше/больше тех которые сохранены в другом, обновляем в нем значения _min и _max.

Компилируем проект и в меню Build обнаруживаем пункт Deploy, который разместит созданный нами агрегат в целевой базе данных.

Осталось только оттестировать созданный нами агрегат в базе данных.

Полный код пользовательского агрегата приведен ниже: