 |
Демонстрационная база данных
|
|
|
|
Новые возможности T-SQL в MS SQL Server 2005
Гайдар Магдануров
Предисловие
В 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 ON P.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 1 2 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 int DECLARE @Level int SELECT TOP(1) @CurrentID = BrandID FROM Brands ORDER BY BrandID DECLARE @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 = 1 WHILE @Level > 0 BEGIN IF EXISTS (SELECT * FROM @StackTable WHERE Level = @Level) BEGIN SELECT @CurrentID = ID FROM @StackTable WHERE Level = @Level INSERT INTO @OutputTable SELECT BrandID, [Name], ParentID, @Level AS Level FROM Brands WHERE BrandID = @CurrentID DELETE FROM @StackTable WHERE Level = @Level AND ID = @CurrentID INSERT @StackTable SELECT BrandID, @Level + 1 FROM Brands WHERE ParentID = @CurrentID IF @@ROWCOUNT > 0 SET @Level = @Level + 1 END ELSE SET @Level = @Level - 1 END SELECT * FROM @OutputTable ORDER BY ID |
Как видит читатель, код без использования виртуальных представлений более громоздкий и сложный для понимания. Несмотря на то, что подобный код несложно написать, весьма просто допустить досадную ошибку, на поиски которой может уйти много времени.
Кроме того, рекурсия с использованием виртуального представления значительно превосходит в производительности подход с использованием временных таблиц. Так, для тестового набора из ста тысяч записей в таблице Brands, время выполнения кода с использованием виртуального представления оказалось в четыре раза меньше, чем время выполнения кода без использования оного. Примерно то же отношение получено и для меньших наборов данных.
|
|
|
12 |
