Гайдар Магдануров
Предисловие
В 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 в 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 позволяют осуществить выборку данных, общих или различных для нескольких наборов данных. Синтаксис новых операторов абсолютно аналогичен оператору 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) мало отличаются в обоих случаях.
Иногда, при написании сложных процедур для работы с данными, удобно использовать функции, возвращающие не скалярное значение, а таблицу. В предыдущих версиях 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 можно достичь желаемого результата: