Silicon Taiga: Расширения языка Transact-SQL

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

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

Другое новшество относится к оператору TOP. В SQL Server 2000 для этого оператора использовались фиксированные константы - TOP 5 (верхние 5 строк). А в SQL 2005 к оператору TOP можно привязывать любое выражение в пределах правил Transact-SQL, включая использование переменного или скалярного подзапроса. Оператор TOP поддерживают предложения INSERT, UPDATE и DELETE.

В SQL 2005 усовершенствована обработка аварийного прерывания: язык Transact-SQL дополнен новыми предложениями Try...Catch...Finally. Их использование позволяет отслеживать ошибки, вызвавшие прерывание, без потери контекста транзакции, что обеспечивает полное ее восстановление. Обработка прерывания возможна и в SQL Server 2000, но без сохранения детального контекста прерванной транзакции, что делает невозможным ее полное восстановление.

Кроме того, значительной переработке подверглись инструкции DDL (Data Definition Language, язык определения данных). Например, для того чтобы создать любой объект, необходимо воспользоваться только операторами языка DDL, а не использовать хранимые процедуры, как было до MS SQL 2005.

Новые операторы языка определения данных (DDL)

В новой версии сервера решена проблема единства методов создания, модификации и удаления объектов.

Все объекты сервера создаются оператором CREATE, модифицируются оператором ALTER, а удаляются оператором DROP. Данный подход стандартизует методы управления объектами, что упрощает разработку приложений и администрирование системы.

Новые операторы языка определения данных приведены в табл. 1.1, а их использование будет описано в следующих главах книги.

Тип объекта	Создание	Модификация	Удаление
Синоним	CREATE SYNONYM		DROP SYNONYM
Сборка	CREATE ASSEMBLY	ALTER ASSEMBLY	DROP ASSEMBLY
Тип данных	CREATE TYPE		DROP TYPE
Пользователь	CREATE USER	ALTER USER	DROP USER
Роль	CREATE ROLE	ALTER ROLE	DROP ROLE
Схема	CREATE SCHEMA	ALTER SCHEMA	DROP SCHEMA
Контракт	CREATE CONTRACT	ALTER CONTRACT	DROP CONTRACT
Очередь	CREATE QUEUE	ALTER QUEUE	DROP QUEUE
Служба	CREATE SERVICE	ALTER SERVICE	DROP SERVICE
Маршрут	CREATE ROUTE	ALTER ROUTE	DROP ROUTE
Логин	CREATE LOGIN	ALTER LOGIN	DROP LOGIN
Точка доступа	CREATE ENDPOINT		DROP ENDPOINT
Сертификат	CREATE CERTIFICATE	ALTER CERTIFICATE	DROP CERTIFICATE

Табл. 1.1. Перечень новых DDL-операторов

Новые операторы языка манипулирования данными (DML)

Язык манипулирования данными (Data Manipulation Language, DML) - самый используемый модуль команд языка Transact-SQL. В девяноста случаях из ста применяется именно он. В языке появилось много новых конструкций: CTE-выражения, PIVOT, UNPIVOT, TOP и многие другие. Основные новшества, появившиеся в языке, будут рассмотрены в этом разделе.

CTE-выражения

Часто возникает потребность создания выражений для временных вычислений. Однако в предыдущих версиях сервера такое выражение, не оформленное как хранимая процедура, функция или просмотр (View), рассматривалось как динамическая строка и компилировалось при каждом вызове. Потребность в рекурсивных вычислениях решалась либо через курсоры, либо через операции с временными таблицами.

Для решения этих задач в новой версии сервера могут быть использованы CTE-выражения (common table expression). Эта новинка позволяет определить виртуальное представление, которое можно использовать в другом операторе (например, SELECT) языка манипулирования данными.

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

 WITH  ::= expression_name [( column_name [ ,...n ])] AS ( CTE_query_definition )

Пример использования CTE-выражения для упрощения читаемости запросов показан в листинге 1.1.

 USE AdventureWorks; WITH DirReps(ManagerID, DirectReports) AS ( 	 SELECT ManagerID, COUNT(*) 	 FROM HumanResources.Employee AS e 	 WHERE ManagerID IS NOT NULL 	 GROUP BY ManagerID ) SELECT * FROM DirReps ORDER BY ManagerID;

Важной особенностью CTE является то, что при многократном использовании CTE в запросе сервер может оптимизировать вызовы и вызывать ее только один раз (листинг 1.2).

 USE AdventureWorks; WITH low AS ( SELECT ((max (UnitPrice*OrderQty))/3) AS v FROM Sales.SalesOrderDetail), high AS ( SELECT (2 *max(UnitPrice*OrderQty)/3) AS v FROM Sales.SalesOrderDetail) SELECT SalesOrderID, low.v, high.v FROM Sales.SalesOrderDetail as sod, low, high WHERE sod.UnitPrice*OrderQty > low.v AND sod.UnitPrice*OrderQty <= high.v;

Но, наверное, одно из самых полезных применений CTE, - выполнение рекурсивных вычислений, в том числе по древовидным структурам (рис. 1.1).

Использование CTE для рекурсивного прохода по дереву, представленному в виде таблицы (рис. 1.2), показано в листинге 1.3.

 USE AdventureWorks; WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS ( - "Якорь" SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel FROM HumanResources.Employee WHERE ManagerID IS NULL 	UNION ALL - Рекурсивный элемент SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1 FROM HumanResources.Employee e INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID ) SELECT * FROM DirectReports;

Запрос выводит идентификаторы начальника и подчиненного и уровень подчиненности (рис. 1.3).

Рис. 1.3. Результаты использования CTE для рекурсивного прохода по дереву

Операторы PIVOT и UNPIVOT

SQL Server 2005 представляет два новых реляционных оператора - PIVOT и UNPIVOT, которые могут использоваться в выражении FROM. Эти операторы выполняют определенные преобразования над входным набором данных и выдают результирующий набор.

Оператор PIVOT превращает строки в столбцы, выполняя при необходимости агрегацию. Он расширяет входной набор, основываясь на заданном для преобразования столбце и создавая набор вывода со столбцом для каждого уникального значения в столбце для преобразования. В сценарии с открытой схемой вы поддерживаете объекты с наборами свойств, либо неизвестными заранее, либо различными для каждого типа объектов. Пользователи приложения определяют свойства динамически. Вместо предопределения многих столбцов и хранения NULL в таблицах, свойства разносятся по строкам и хранятся только свойства, относящиеся к определенному экземпляру объекта. PIVOT позволяет создавать матричные отчеты для открытой схемы и других сценариев, в которых вы превращаете строки в столбцы, выполняя при этом агрегацию и представляя данные в нужной форме.

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

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

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

 ::= table_source PIVOT  table_alias  ::= ( aggregate_function(value_column) FOR pivot_column IN ( ) )

 ::= table_source UNPIVOT  table_alias  ::= ( value_column FOR pivot_column IN (  ) )  ::= column_name [, ...]

Предположим, надо посчитать, продажу какого количества продуктов с номерами 776, 777, 774 оформили сотрудники отдела продаж.

Рассмотрим пример применения операции PIVOT (листинг 1.4) к набору данных, показанному на рис. 1.4.

 USE AdventureWorks; SELECT [SalesPersonID], [776], [777], [774] INTO [Sales].[tmpUnpivot] 	 FROM ( SELECT [OrderQty] ,[ProductID],[SalesPersonID] 	 FROM [AdventureWorks].[Sales].[SalesOrderDetail] as od JOIN [AdventureWorks].[Sales].[SalesOrderHeader] as o ON o.[SalesOrderID] = od.[SalesOrderID] ) as p PIVOT ( COUNT ([OrderQty]) FOR [ProductID] IN ( [776], [777], [774]) ) AS pvt;

ПРИМЕЧАНИЕ

Обратите внимание, что при выполнении запроса, показанного в листинге 1.4, создается постоянная таблица [Sales].[tmpUnpivot]. Это необходимо для следующего запроса (листинг 1.5).

Результат выполнения оператора PIVOT показан на рис. 1.5.

Рассмотрим пример применения оператора UNPIVOT (листинг 1.5) к набору данных, показанному на рис. 1.5.

 USE AdventureWorks; SELECT [SalesPersonID], ProductID, Quantity FROM 	( SELECT [SalesPersonID],[776] ,[777] ,[774] 		 FROM [AdventureWorks].[Sales].[tmpUnpivot]) pvt UNPIVOT (Quantity FOR ProductID 		 IN ([776] ,[777] ,[774]) 	) unpvt;

Оператор UNPIVOT создал обратный транспонированный набор данных (рис. 1.6).

Ключевое слово OUTPUT

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

OUTPUT может использоваться в операторах INSERT, UPDATE, DELETE:

Доступ к данным в этих таблицах возможен через table_name.column_name.

 ::= { 	OUTPUT  [ ,...n ] 	 INTO @table_variable }  ::= {  / scalar_expression }  ::= { DELETED / INSERTED / from_table_name } . { * / column_name }

Рассмотрим пример использования ключевого слова OUTPUT (листинги 1.6-1.8).

 USE AdventureWorks; DECLARE @MyTableVar TABLE (ScrapReasonID smallint, 	 Name varchar(50), 	 ModifiedDate datetime);

 USE AdventureWorks; INSERT INTO Production.ScrapReason 	OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N 'Operator error' , GETDATE());

 SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;

В результате создается временная служебная таблица inserted (рис. 1.7).

ПРИМЕЧАНИЕ

Поскольку поле ScrapReasonID представляет столбец таблицы Production.ScrapReason, для которого установлено свойство IDENTITY (автогенерация значений), то, во-первых, его можно не указывать в операции вставки, а во-вторых, его значение может отличаться от показанного на рис. 1.7.

Оператор APPLY

В MS SQL Server 2000 впервые появились пользовательские функции: скалярные, подставляемые табличные и многооператорные табличные.

Оператор APPLY позволяет ссылаться на табличную функцию в коррелированном подзапросе и вызывать определенную табличную функцию один раз для каждой строки внешнего табличного выражения. APPLY определяется в выражении FROM запроса аналогично реляционному оператору JOIN.

APPLY используется в двух вариантах: CROSS APPLY и OUTER APPLY.

CROSS APPLY вызывает табличную функцию для каждой строки внешнего выражения. На столбцы внешней таблицы можно ссылаться, как на аргументы табличной функции. CROSS APPLY возвращает унифицированный набор результатов, собранный из всех результатов, возвращенных отдельными вызовами табличной функции. Если табличная функция возвращает пустой набор для данной внешней строки, то эта строка в результате не возвращается.

OUTER APPLY похож на CROSS APPLY, но возвращает строки из внешнего набора, даже если табличная функция возвратила пустой набор. Возвращаются значения NULL как значения столбцов, соответствующих столбцам табличной функции.

CROSS APPLY - возвращает строки, присутствующие одновременно и в таблице, и в функции.

OUTER APPLY - возвращает все строки таблицы независимо от того, возвращаются или нет соответствующие им строки из функции.

left_table_source { CROSS / OUTER } APPLY right_table_source

Применение операторов CROSS APPLY и OUTER APPLY показано в листингах 1.9-1.11.

 USE AdventureWorks; GO CREATE FUNCTION Sales.MostRecOrders(@intCustomerID as int) RETURNS TABLE AS RETURN 	 SELECT TOP (2) SalesOrderID, OrderDate 	 FROM Sales.SalesOrderHeader WHERE CustomerID = @intCustomerID ORDER BY OrderDate DESC ;

 USE AdventureWorks; SELECT Name, mro.* FROM Sales.Store CROSS APPLY Sales.MostRecOrders(CustomerID) as mro;

Результат запроса на CROSS-объединение показан на рис. 1.8.

Рис. 1.8. Результат запроса на CROSS-объединение

 USE AdventureWorks; SELECT Name, mro.* FROM Sales.Store OUTER APPLY Sales.MostRecOrders(CustomerID) as mro;

Результат запроса на OUTER-объединение показан на рис. 1.9.

Рис. 1.9. Результат запроса на OUTER-объединение

В результирующем наборе появились значения NULL, значит, функция не возвратила значения, соответствующие значениям в таблице (подобно функции LEFT OUTER JOIN между таблицами).

Функции ранжирования

Иногда необходимо упорядочить наборы данных частично, но стандартный оператор ORDER BY лишен такой возможности.

В новой версии сервера появились четыре функции ранжирования выводимых данных в запросах: RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(). Ранжирование выполняется внутри "окна", определяемого предложением PARTITION BY.

Функции RANK()

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

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

Рассмотрим на примере, как функция RANK() ранжирует множество продуктов по цене продукта внутри "окна", определяемого названием категории (листинг 1.12).

 USE AdventureWorks; SELECT p.Name as Product, p.ListPrice, psc.Name as Category, RANK() OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Rank' FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции RANK() показан на рис. 1.10.

Однако RANK() может давать некорректный результат; если несколько строк имеют один и тот же rank, то rank следующей строки назначается исходя из количества строк, попавших в "окно" (рис. 1.10). Для решения этой проблемы можно пользоваться функцией DENSE_RANK().

Функции DENSE_RANK()

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

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

Эта проблема может быть решена с помощью функции DENSE_RANK() (листинг 1.13).

 USE AdventureWorks; SELECT p.Name as Product, p.ListPrice, psc.Name as Category, DENSE_RANK() OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Rank' FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции DENSE_RANK() показан на рис. 1.11.

Рис. 1.11. Результат выполнения функции DENSE_RANK()

Функции ROW_NUMBER()

Функции ROW_NUMBER() определяют место объекта внутри группы однородных объектов, упорядоченных по какому-либо признаку.

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

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

В листинге 1.14 решается задача определения места товара внутри категории по его цене.

 USE AdventureWorks; SELECT ROW_NUMBER() OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Rank' , psc.Name as Category, p.Name as Product, p.ListPrice FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции ROW_NUMBER() показан на рис. 1.12.

Рис. 1.12. Результат выполнения функции ROW_NUMBER()

Функции NTILE()

Функция NTILE() выполняет автоматическое разделение множества объектов по группам (категориям) на основании какого-либо признака, например определяет количество ценовых групп товаров по категориям и принадлежность каждого товара к группе.

Функция NTILE() делит "окно" (группу), определяемое предложением PARTITION BY, на указанное количество рангов, причем функция "старается", чтобы количество строк в каждом из рангов было одинаковым. Для каждой строки возвращается номер ранга, которому она принадлежит (листинг 1.15).

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

 USE AdventureWorks; SELECT NTILE(3) OVER (PARTITION BY psc.Name ORDER BY p.ListPrice DESC ) as 'Price Band' , psc.Name as Category, p.Name as Product, p.ListPrice FROM Production.Product as p INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID;

Результат выполнения функции NTILE() показан на рис. 1.13.

Оператор TOP

В новой версии сервера оператор TOP можно использовать в любых предложениях языка DML, а не только в предложении SELECT, аргументом может быть не только число, но и выражение. Все остальные конструкции оператора остались без изменений. Пример использования оператора TOP показан в листинге 1.16.

Оператор TOP возвращает "верхние" строки набора в соответствии с порядком сортировки, указанным в предложении ORDER BY.

[ 	 TOP (expression) [ PERCENT ] [ WITH TIES ] ]

 USE AdventureWorks; DECLARE @var1 AS int, @var2 AS int; SET @var1 = 4; SET @var2 = 2; SELECT TOP (@var1*@var2) * FROM HumanResources.Employee;

Результатом выполнения оператора TOP будет набор из 8 (4 х 2) строк, показанный на рис. 1.14.

Предложение TABLESAMPLE

С помощью предложения TABLESAMPLE можно выбирать множество строк, в отличие от оператора TOP не "верхние" строки, а произвольные. Структура набора сохраняется до тех пор, пока не произойдут изменения в строках. Указав номер набора в операторе REPEATABLE, можно повторно возвращать один и тот же набор.

 ::= 	TABLESAMPLE SYSTEM (sample_number [ PERCENT / ROWS ]) REPEATABLE (seed)

В листинге 1.17 приведен пример использования предложения TABLESAMPLE.

 USE AdventureWorks; SELECT * FROM HumanResources.Employee SYSTEM TABLESAMPLE (10 PERCENT );

ПРИМЕЧАНИЕ

В результате вместо ожидаемых 29 строк (в таблице HumanResources.Employee 291 строка) мы можем увидеть 41, 42 и даже 82 строки в зависимости от того, с каких страниц производилась выборка.

Новые возможности в обработке ошибок

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

Работа с ошибками была утомительна еще из-за переменной @@ERROR, устанавливаемой на каждый оператор и проверяемой после выполнения каждой операции.

Блок TRY…CATCH

В новую версию сервера добавлен блок TRY…CATCH для обработки исключительных ситуаций (ошибок).

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

 BEGIN TRY 	{ sql_statement / statement_block } END TRY BEGIN CATCH 	{ sql_statement / statement_block } END CATCH [ ; ]

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

 CREATE PROCEDURE usp_someproc AS 	 BEGIN 		 BEGIN TRY 			 SELECT * FROM titles; 		 END TRY 		 BEGIN CATCH 			 SELECT ERROR_NUMBER(); 		 END CATCH 	 END ; GO

Внутри блока-"ловушки" доступны новые функции для обработки исключений:

Таким образом, можно получить не только номер ошибки (как при использовании @@ERROR), а всю доступную информацию для обработки и отправки на клиентское приложение.

Функция XACT_STATE()

Новая функция XACT_STATE(), возвращающая информацию о состоянии транзакции, используется для обработки оператора, логическая часть которого попадает в блок-"ловушку" и может приводить к невыполнимой транзакции.

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

 CREATE PROCEDURE usp_someproc AS BEGIN TRY 	 SELECT * FROM authors; END TRY BEGIN CATCH 	 IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION ; 	 IF (XACT_STATE()) = 1 COMMIT TRANSACTION ; END CATCH; GO

Состояние XACT_STATE() = 0 обозначает, что нет открытых транзакций.

Новый тип триггеров - DDL-триггер

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

DDL-триггеры, в отличие от стандартных DML-триггеров, "срабатывают" на события, порождаемые операциями создания (CREATE), модификации (ALTER) и удаления (DROP) объектов 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-триггера приведен в листингах 1.20-1.22.

 USE AdventureWorks; CREATE TABLE [Sales].[testDDLTrigger]( 	[ID] int NULL , 	[Description] varchar(100) NULL );

 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS 	 PRINT N 'Таблицы не могут быть модифицированы или удалены' 	 ROLLBACK TRAN ;

 DROP TABLE [Sales].[testDDLTrigger];

Полученное в результате сообщение об ошибке показано на рис. 1.15.

Резюме

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

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