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

Посмотреть архив целиком

Новые возможности 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, время выполнения кода с использованием виртуального представления оказалось в четыре раза меньше, чем время выполнения кода без использования оного. Примерно то же отношение получено и для меньших наборов данных.

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

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

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

SELECT B.BrandID FROM Brands B

EXCEPT

SELECT P.BrandID FROM Products P


Случайные файлы

Файл
151740.rtf
89645.rtf
122716.rtf
22394.rtf
94984.rtf




Чтобы не видеть здесь видео-рекламу достаточно стать зарегистрированным пользователем.
Чтобы не видеть никакую рекламу на сайте, нужно стать VIP-пользователем.
Это можно сделать совершенно бесплатно. Читайте подробности тут.