Добро пожаловать в мир новых возможностей Microsoft SQL Server 2005!

С тех пор как представители Microsoft объявили об интеграции кода CLR в SQL Server 2005, разработчики проявляют большой интерес к тому, как это скажется на разработке базы данных. Возникло предположение, что Transact-SQL уйдет со сцены. Однако это не так. Он по-прежнему остается единственным средством извлечения данных и манипулирования ими. Конечно, можно писать хранимые процедуры на других языках, таких как C#, но, если необходимо извлекать или изменять данные, мы вынуждены использовать Transact-SQL.

Вместе с тем язык Transact-SQL подвергся переработке с целью приведения его в соответствие со стандартом ANSI и расширения функциональных возможностей. В результате этой переработки язык стал более логичным и завершенным. Например, для того чтобы создать любой объект, требуется воспользоваться только операторами языка DDL и не задействовать хранимые процедуры, как было до версии SQL Server 2005. Язык, как и раньше, состоит из трех модулей: языка определения данных (Data definition language), языка манипулирования данными (Data manipulation language) и языка управления данными (Data control language).

Все листинги в этой статье показаны на примере базы AdventureWorks, поставляемой вместе с Microsoft SQL Server 2005 взамен ушедших в отставку Northwind и Pubs.

В новой версии SQL Server наконец решена проблема единства методов создания, модификации и удаления объектов. Все объекты сервера создаются оператором CREATE, модифицируются оператором ALTER, а удаляются — оператором DROP. Данный подход стандартизирует методы управления объектами, что упрощает разработку приложений и администрирование системы. Ниже приведен ряд новых операторов языка DDL (табл. 1), а использование этих операторов будет рассмотрено в другой статье.

Наверное, самым значительным изменениям подвергся язык манипулирования данными. Добавлено много новых конструкций, а существующие переработаны. Многие из добавлений будут очень полезны и можно сказать, что именно им посвящена данная статья.

Использование CTE

В предыдущих версиях SQL Server часто возникала потребность создавать выражения для временных вычислений. Однако, если такое выражение не оформлялось как хранимая процедура (storage procedure), функция (function) или представление (view), SQL Server «относился» к нему как к динамической строке и компилировал при каждом вызове. Кроме того, часто возникала потребность в рекурсивных вычислениях, которая решалась либо через курсоры, либо через операции с временными таблицами. Для решения этих задач в новой версии сервера могут быть использованы выражения CTE (common table expression).

СТЕ является временным именованным результирующим набором, который конкретизируется запросом запуска в предложении WITH, может служить заменой субзапросу или использоваться во View; его можно задействовать с SELECT/INSERT/UPDATE/

DELETE и использовать для рекурсивных вычислений. CTE начинается с предложения WITH, за которым следует AS и его тело. Множественные CTE отделяются запятыми:

WITH

::= expression_name

[( column_name [ ,...n ])]

AS

( CTE_query_definition )

Пример использования CTE-выражения для повышения читаемости запросов приведен в листинге 1. Важная особенность CTE заключается в том, что при многократном его применении в запросе сервер может оптимизировать его вызовы и вызывать только один раз. Но, наверное, одним из самых полезных применений CTE будет выполнение рекурсивных вычислений, в том числе «проходы» по древовидным структурам.

Например, таблица учетных записей или сводные показатели, сумма остатков и т. д.

Рекурсивное выражение CTE содержит три части:

  • якорь, за которым следует UNION ALL, — производит инициализацию;
  • рекурсивный элемент после UNION ALL — выполняет рекурсию;
  • внешний SELECT — отбирает результаты, которые должны быть возвращены процессору запросов.

В листинге 2 показано использование CTE для прохода по дереву, оформленному в виде таблицы HumanResources.Employee из базы AdventureWorks. Запрос выводит идентификаторы сотрудника, его менеджера и уровень подчиненности (см. табл. 2.)

Pivot и Unpivot

Очень часто при создании систем обработки данных, особенно при переносе данных из других источников, возникает проблема «поворота» данных или превращения столбцов в строки и обратно. Для выполнения этих действий служат новые реляционные операторы PIVOT и UNPIVOT. Оператор PIVOT переводит строки в столбцы, агрегирует значения и порождает кросстабличные отчеты. Оператор UNPIVOT переводит столбцы в строки, хотя может быть не всегда симметричен PIVOT.

Для работы с оператором Pivot необходимо определить:

  • столбец, который содержит значение для «повернутой» таблицы (FOR pivot_column);
  • столбцы, которые будут представлять «повернутую» таблицу (IN () );
  • агрегативную колонку (aggregate_function(value_column)).

Для работы с оператором Unpivot необходимо определить:

  • столбец, который содержит агрегированное значение (value_column );
  • столбец, для которого будет осуществлен «поворот» (FOR pivot_column );
  • столбцы, которые будут представлять строки «повернутой» таблицы (IN () ).

Синтаксис команды PIVOT такой:

::=

table_source PIVOT table_alias

::=

( aggregate_function(value_column)

FOR pivot_column

IN ( )

А команды UNPIVOT такой:

::=

table_source UNPIVOT table_alias

::=

( value_column FOR pivot_column

IN ( )

)

::=

column_name [, ...]

Предположим, нам нужно выполнить запрос: «Продажу какого количества товаров с номерами 776, 777, 774 оформили сотрудники?» Рассмотрим пример операции Pivot к набору данных, приведенному в табл. 3. Операция Pivot будет иметь вид, показанный в листинге 3. Результат выполнения запроса представлен в табл. 4. Теперь набор из табл. 4 обработаем при помощи UNPIVOT (см. листинг 4). Мы увидим, что оператор UNPIVOT породил обратный транспонированный набор данных.

OUTPUT

Тот, кто имел опыт работы с триггерами в предыдущих версиях SQL Server, сталкивался со специальными внутренними служебными таблицами inserted и deleted, используемыми в них. Подобная возможность добавлена в SQL Server 2005 для операторов языка DML, выполняющих модификацию данных. Для того чтобы воспользоваться этой возможностью, необходимо в DML-выражение добавить предложение OUTPUT:

  • OUTPUT может появляться в операторах INSERT, UPDATE, DELETE;
  • при появлении его в операторе INSERT создается временная таблица inserted;
  • при появлении его в операторе DELETE создается временная таблица deleted;
  • при появлении его в операторе UPDATE создаются временные таблицы inserted и deleted;
  • доступ к данным в этих таблицах возможен через table_name.column_name.

Синтаксис команды такой:

::=

{

OUTPUT [ ,...n ]

INTO @table_variable

}

::=

{ | scalar_expression }

::=

{ DELETED | INSERTED | from_table_name } . { * | column_name }

Рассмотрим это на примере: создадим временную таблицу, выполним вставку данных, просмотрим содержимое таблицы @MyTableVar (см. листинги 5-7). Обратите внимание на то, как используется ключевое слово OUTPUT и временная служебная таблица inserted, которая создается при выполнении оператора INSERT. При выполнении операции DELETE будет использоваться таблица deleted, а при операции UPDATE — обе таблицы inserted и deleted.

Применение оператора APPLY

Впервые пользовательские функции появились в SQL Server 2000, где было предложено три типа функций: скалярные (scalar), подставляемые табличные (In-Line Table) и многооператорные табличные (Multi-statement table). Подставляемые и многооператорные табличные функции возвращают результирующий набор, который далее может быть объединен с другими данными. Для того чтобы сделать это объединение более удобным, в новой версии SQL Server применен оператор APPLY, который позволяет применить пользовательскую табличную функцию к каждой строке объединяемого набора. Существует два типа операторов:

  • CROSS APPLY — возвращающий только строки, присутствующие одновременно в наборе и функции;
  • OUTER APPLY — возвращающий все строки набора независимо от того, возвращаются или нет соответствующие им строки из функции.

Синтаксис команды выглядит так:

left_table_source { CROSS | OUTER } APPLY right_table_source

Пример создания пользовательской табличной функции приведен в листинге 8. Запрос на CROSS-объединение из листинга 9 приводит к получению данных табл. 5. А запрос на OUTER-объединение листинга 10 дает другие данные (см. табл. 6). Обратите внимание, что в результирующем наборе появились значения NULL. Это указывает на то, что функция не возвратила значений, сответствующих значениям в наборе (это похоже на применение LEFT OUTER JOIN)

Использование функций ранжирования. Иногда возникает потребность производить частичное упорядочение наборов данных, однако стандартный оператор языка ORDER BY такой возможности лишен. В новой версии SQL Server появились четыре новые функции, предназначенные для ранжирования выводимых данных в запросах. Это функции RANK(), DENSE_RANK(), ROW_NUMBER() и NTILE(). Ранжирование выполняется внутри «окна» (группы), определяемого предложением PARTITION BY. Рассмотрим их использование на примерах.

Функция RANK() возвращает ранг (порядковый номер) каждой строки внутри «окна», определяемого предложением PARTITION BY. Ранг каждой строки есть ранг предыдущей плюс единица. Синтаксис такой:

RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Однако RANK() может давать некорректный результат; если несколько строк имеют один и тот же ранг, тогда ранг следующей строки назначается исходя из количества строк, попавших в «окно».

Функция DENSE_RANK() возвращает ранг каждой строки внутри «окна», определяемого предложением PARTITION BY без пропусков в ранжировании. Ранг каждой последующей строки есть отличимый ранг предыдущей плюс единица.

Синтаксис такой:

DENSE_RANK ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Функция ROW_NUMBER() возвращает последовательный номер строки внутри «окна», определяемого предложением PARTITION BY, начиная с единицы для каждой первой строки внутри каждого нового «окна». Синтаксис такой:

ROW_NUMBER ( ) OVER ( [ < partition_by_clause > ] < order_by_clause > )

Очень часто возникает потребность получить место, занимаемое объектом внутри группы однородных объектов, упорядоченных по какому-либо признаку. В примере, приведенном в листинге 11, решается задача определения места товара внутри категории по его цене.

Функция NTILE() делит «окно» (группу), определяемую предложением PARTITION BY, на указанное количество рангов integer_expression, причем по возможности так, чтобы количество строк в каждой из групп было одинаковым. Для каждой строки возвращается номер группы, которой она принадлежит. Синтаксис такой:

NTILE (integer_expression) OVER ([< partition_by_clause>])

Часто возникает потребность разделить множество объектов по группам (категориям) на основании какого-либо признака. Скажем, необходимо найти количество ценовых групп товара по категориям и определить принадлежность каждого товара к группе. Данная задача может быть решена при помощи функции NTILE(). Ее использование показано на примере в листинге 12.

Новые возможности предложения TOP

В новой версии SQL Server расширен синтаксис и возможности TOP. Предложение TOP возвращает «верхние» строки набора в соответствии с порядком сортировки, указанным в предложении ORDER BY. Суть расширения возможностей состоит в том, что теперь TOP можно применять в любых предложениях языка DML, а не только в предложении SELECT. Кроме того, в качестве аргумента можно использовать не только число, но и выражение. Все остальные конструкции оператора остались без изменений. Синтаксис выглядит следующим образом:

[

TOP (expression) [PERCENT] [ WITH TIES ]

]

Пример использования TOP приведен в листинге 13. В результате выполнения получим набор из восьми строк.

Новое предложение TABLESAMLE

С помощью этого предложения можно выбирать множество строк, указанное либо в процентах, либо в строках. В отличие от TOP, предложение TABLESAMLE выбирает не «верхние» строки, а произвольные. Структура набора сохраняется до тех пор, пока не произойдут изменения в строках. Указав номер набора в операторе REPEATABLE, можно повторно возвращать один и тот же набор. Синтаксис:

::=

TABLESAMPLE (sample_number [ PERCENT | ROWS ] )

[ REPEATABLE (repeat_seed) ]

Пример использования приведен в листинге 14. В результате его выполнения из таблицы HumanResources. Employee будет возвращено 10 произвольных строк.

Новые подходы к обработке ошибок

В предыдущей версии SQL Server обработка ошибок была не совсем удобна, поскольку нельзя было создать пользовательский обработчик ошибок (перехватить событие ошибки), и для этого либо пользовались триггерами, либо создавали обработчик на клиентском приложении, что никак не соответствовало требованиям объектно-ориентированного программирования и многоуровневой клиент-серверной архитектуре.

Работа с ошибками в предыдущих версиях SQL Server была утомительна еще из-за переменной @ERROR, устанавливаемой на каждый оператор и проверяемой после выполнения каждой операции. В новую версию добавлена возможность обработки исключительных ситуаций (ошибок) при помощи блока TRY...CATCH, к которому привыкли разработчики, использующие продукты Microsoft.

Обработчик состоит из TRY-блока, где находится выполняемый код, и CATCH-блока, где происходит перехват ошибки.

Синтаксис:

BEGIN TRY

{ sql_statement | statement_block }

END TRY

BEGIN CATCH

{ sql_statement | statement_block }

END CATCH

[ ; ]

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

Обратите внимание на новые функции для обработки исключений, доступные внутри блока-«ловушки»:

  • ERROR_NUMBER() — номер ошибки;
  • ERROR_SEVERITY() — уровень серьезности;
  • ERROR_STATE() — номер состояния ошибки;
  • ERROR_MESSAGE() — полный текст сообщения об ошибке.

Таким образом можно получить не только номер ошибки (как при использовании @@ERROR), но и всю доступную информацию для обработки и отправки на клиентское приложение. Для того чтобы обработать оператор, логическая часть которого попадает в блок-«ловушку» и может приводить к невыполнимой транзакции, используется новая функция XACT_STATE(), возвращающая состояние транзакции. Состояние XACT_STATE() = 0 обозначает, что открытых транзакций нет.

Новые типы триггеров

В рассматриваемой версии SQL Server появились новые виды триггеров, потребность в которых давно ощущалась разработчиками приложений, это DDL-триггеры. DDL-триггеры, в отличие от стандартных DML-триггеров, «срабатывают» на операции создания (CREATE), модификации (ALTER) и удаления (DROP) объектов MS SQL Server. Эти триггеры могут применяться для программирования приложений, однако чаще всего они используются для администрирования. Все события SQL Server делятся на серверные события и события базы данных. Полный перечень событий, на которые реагирует триггер, можно найти в справочной системе по MS SQL Server 2005.

Синтаксис триггера такой:

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > }

[ ; ]

::=

assembly_name.class_name[.method_name]

Приведем пример использования DDL-триггера, для этого создадим пробную таблицу и триггер, а затем попытаемся удалить таблицу (см. листинги 16-18). В результате получим сообщение о том, что таблицы не могут быть модифицированы или удалены, что говорит о срабатывании триггера.

Заключение

В данной статье представлены только основные изменения, внесенные в структуру языка Transact-SQL. Остальные конструкции языка тоже дополнены новыми возможностями и будут рассматриваться в следующих статьях. В целом язык Transact-SQL стал значительно мощнее и приобрел новые функциональные свойства, что, несомненно, укрепит позиции SQL Server на рынке.

Александр Каленик. Старший преподаватель Центра компьютерного обучения «Специалист» при МГТУ им. Н.Э. Баумана