Смекни!
smekni.com

Новые возможности T-SQL в MS SQL Server 2005 (стр. 1 из 3)

Гайдар Магдануров

Предисловие

В MS SQL Server 2005 появилось множество новшеств, позволяющих еще более продуктивно использовать эту СУБД. Многие из них, такие как новые типы данных, интеграция с платформой .NET, поддержка XML, новые функции ранжирования, улучшения в системе безопасности и прочее, уже были ранее описаны в журнале RSDN Magazine [1, 2, 3, 4, 5]. В этой статье будут рассмотрены новые операторы и функции работы с данными. В связи с грядущим в ноябре выходом финальной версии, приведенная информация является предельно актуальной для всех разработчиков, использующих SQL Server.

Помимо описания новых возможностей в статье приведены примеры кода, реализующие сходную функциональность, но работающие в предыдущих версиях SQL Server, поэтому эта статья может быть полезна не только читателям, планирующим использование SQL Server 2005, но и пользователям более ранних версий этой СУБД.

Демонстрационная база данных

В статье не будут рассматривать стандартные демонстрационные базы данных, поставляемые с SQL Server (Northwind, AdventureWorks). Для наибольшей наглядности примеров, создадим небольшую базу данных некоторого воображаемого магазина, торгующего ноутбуками.

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

Для реализации этих желаний будет использована база данных состоящая из четырех таблиц: Brands - справочник производителей, позволяющий отслеживать отношения компаний с точки зрения «родительская-дочерняя»; Products – таблица имеющихся на складе моделей, содержащая необходимую информацию о цене, количестве, названии и базовой конфигурации; Orders – таблица, содержащая информацию о заказах; QrdersQueue – таблица, реализующая функциональность очереди заказов.

CREATE TABLE [dbo].[Brands]( [BrandID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](32) NOT NULL, [ParentID] [int] NULL DEFAULT ((0)))CREATE TABLE [dbo].[Orders]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [Date] [datetime] NOT NULL, [ProductID] [int] NOT NULL, [Quantity] [int] NOT NULL DEFAULT ((1)))CREATE TABLE [dbo].[Products]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [BrandID] [int] NOT NULL, [Model] [nvarchar](32) NOT NULL, [Configuration] [nvarchar](128) NOT NULL, [Price] [money] NOT NULL, [Quantity] [numeric](18, 0) NOT NULL)CREATE TABLE [dbo].[OrdersQueue]( [QueueID] [int] IDENTITY(1,1) NOT NULL,[OrderID] [int] NOT NULL)

Новые возможности T-SQL

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

Общие табличные выражения

Общие табличные выражения (Common Table Expressions, CTE) позволяют определять временные именованный набор данных, функционально похожий на представление (View), доступный в пределах пакета (batch).

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

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

WITH ИмяПредставления(ИмяПоля, ИмяПоля, …) AS (Подзапрос)

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

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

Задачу можно решить, используя виртуальное представление

WITH AvgPrice(BrandID, Price) AS (SELECT BrandID, AVG(Price) FROM Products GROUP BY BrandID)SELECT P.Model FROM Products AS P INNER JOIN AvgPrice AS A ONP.BrandID = A.BrandID AND P.Price > A.Price

или используя вложенный подзапрос

SELECT P.Model FROM Products AS P INNER JOIN (SELECT BrandID, AVG(Price) FROM Products GROUP BY BrandID) AS A ON P.BrandID = A.BrandID AND P.Price > A.Price

Как видно из примера, использование CTE и вложенных подзапросов эквивалентно, но, читатель легко может представить ситуацию, когда одно и то же виртуальное представление данных нужно использовать несколько раз в хранимой процедуре, в этом случае единожды объявленное представление с использованием CTE потребует значительно меньше SQL кода.

Рекурсия с использованием виртуальных представлений

Одним из основных преимуществ виртуальных представлений (CTE), является простое и наглядное построение рекурсивных выражений.

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

Для таблицы Brands нужно создать виртуальное представление, использующее рекурсию для получения уровня вложенности:

WITH C (BrandID, [Name], ParentID, NestingLevel) AS( SELECT B.BrandID, B.[Name], B.ParentID, 1 FROM Brands AS B WHERE ParentID = 0 UNION ALL SELECT B.BrandID, B.[Name], B.ParentID, (NestingLevel + 1) FROM Brands AS B INNER JOIN C ON C.BrandID = B.ParentID)SELECT * FROM C

Результатом выполнения запроса будет таблица, например такая:

BrandID Name ParentID NestingLevel-------------------------------------------------- 1 Parent1 0 12 Parent2 0 1 3 Child1 1 2 4 Child11 3 3 5 Child12 3 3

Общий принцип построения рекурсивного выражения

WITH ИмяCTE (Определение) AS( SELECT … -- Выборка с начальным условием, UNION ALL -– Объединение результатов SELECT … -- Выборка определяющаяя шаг рекурсии INNER JOIN CTE.ДочернийID = ИмяТаблицы.РодительскийID –- Присоединение «по родителю»)

Без использования виртуального представления, для достижения того же результата придется написать значительно более сложный запрос:

DECLARE @CurrentID intDECLARE @Level intSELECT TOP(1) @CurrentID = BrandID FROM Brands ORDER BY BrandIDDECLARE @StackTable TABLE (ID int, Level int)DECLARE @OutputTable TABLE (ID int, [Name] nvarchar(32), ParentID int, Level int)INSERT INTO @StackTable VALUES(@CurrentID, 1)SET @Level = 1WHILE @Level > 0BEGIN IF EXISTS (SELECT * FROM @StackTable WHERE Level = @Level) BEGINSELECT @CurrentID = ID FROM @StackTable WHERE Level = @LevelINSERT INTO @OutputTableSELECT BrandID, [Name], ParentID, @Level AS Level FROM Brands WHERE BrandID = @CurrentID DELETE FROM @StackTable WHERE Level = @Level AND ID = @CurrentIDINSERT @StackTable SELECT BrandID, @Level + 1 FROM Brands WHERE ParentID = @CurrentID IF @@ROWCOUNT > 0 SET @Level = @Level + 1 END ELSE SET @Level = @Level - 1ENDSELECT * FROM @OutputTable ORDER BY ID

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

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

Операторы EXCEPT и INTERSECT

Операторы EXCEPT и INTERSECT позволяют осуществить выборку данных, общих или различных для нескольких наборов данных. Синтаксис новых операторов абсолютно аналогичен оператору UNION.

Допустим, необходимо получить BrandID производителей, модели которых не присутствуют в таблице Products. Тогда, применив оператор EXCEPT следующим образом:

SELECT B.BrandID FROM Brands BEXCEPTSELECT P.BrandID FROM Products P

можно достичь того же результата, что и при использовании оператора EXITS в комбинации с оператором отрицания NOT:

SELECT B.BrandID FROM Brands BWHERE NOT EXISTS (SELECT P.BrandID FROM Products P WHERE P.BrandID = B.BrandID)

Аналогично, для того, чтобы получить BrandID производителей, чьи модели присутствуют в таблице Products. Можно использовать оператор INTERSECT:

SELECT B.BrandID FROM Brands BINTERSECTSELECT P.BrandID FROM Products P

а можно и оператор EXIST без отрицания:

SELECT B.BrandID FROM Brands BWHERE EXISTS (SELECT P.BrandID FROM Products P WHERE P.BrandID = B.BrandID)

либо же совсем привычный синтаксис INNER JOIN:

SELECT DISTINCT B.BrandID FROM Brands B INNER JOIN Products P ON B.BrandID = P.BrandID

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

Производительность запроса при использовании новых операторов, практически не отличается от производительности запросов с EXISTS и JOIN. Число чтений (Reads) и время выполнения (Duration) мало отличаются в обоих случаях.

Оператор APPLY

Иногда, при написании сложных процедур для работы с данными, удобно использовать функции, возвращающие не скалярное значение, а таблицу. В предыдущих версиях SQL Server не было возможности в конструкциях JOIN использовать в качестве аргументов вызываемой функции параметры из внешнего запроса. Например, создадим процедуру GetProductDetails:

CREATE FUNCTION GetProductDetails (@ProductID int) RETURNS TABLE ASRETURN SELECT P.Model, P.Configuration, P.Price FROM Products PWHERE P.ProductID = @ProductID

Следующий код, использующий GetProductDetails приведет к ошибке:

-- Внимание! ЭтоткоднеработаетSELECT O.[Date], P.Model, P.Configuration, P.Price FROM Orders O OUTER JOIN GetProductDetails(O.ProductID) AS P ON P.ProductID = O.ProductID

В SQL Server 2005 для подобного использования функций предназначен оператор APPLY. Используя его вместо JOIN можно достичь желаемого результата: