Еще несколько расширений T-SQL в SQL Server 2008

Ицик Бен-Ган (Itzik@SolidQ.com) – преподаватель и консультант, автор книг по T-SQL, имеет звание SQL Server MVP

.

В одной из предыдущих статей я представил несколько небольших новшеств T-SQL в версии SQL Server 2008, в том числе расширенные возможности синтаксиса VALUES, поддержку выделения номера недели из даты по стандарту ISO и функции преобразования между бинарными и символьными типами. На этот раз мы рассмотрим еще четыре дополнительных нововведения: компонуемые DML-запросы, возможность декларации и инициализации переменных одним предложением, сложные операторы присваивания и более надежную информацию о зависимостях объектов.

Компонуемые запросы DML

В версии SQL Server 2005 появилась поддержка синтаксической конструкции OUTPUT, которая позволяет модифицирующим данные командам, таким как INSERT, UPDATE или DELETE возвращать информацию из обрабатываемых строк. Исходная реализация синтаксиса OUTPUT не позволяет фильтровать строки вывода: если команда обработала 50 строк, то и в потоке вывода оператора OUTPUT будет 50 строк. Вся доступная гибкость ограничивалась возможностью выбора возвращаемых для каждой строки атрибутов и определения выражений, преобразующих доступные атрибуты. Кроме того, есть возможность указания на получателя возвращаемой оператором OUTPUT информации: инициатору запроса, в таблицу или и туда, и туда. Поскольку фильтровать строки вывода невозможно, для сохранения подмножества выведенных строк в принимающей таблице необходимо выполнить две операции: вывести строки из OUTPUT в промежуточную таблицу, а затем в принимающую таблицу записать результат выборки из промежуточной таблицы с наложенным фильтром.

SQL Server 2008 расширяет поддержку синтаксиса OUTPUT новой функцией, получившей название «компонуемый DML». В результате мы получаем возможность сразу внести в принимающую таблицу определенное подмножество строк, возвращаемых модифицирующей командой. Компонуемый DML реализован в виде команды INSERT SELECT с возможностью использования оператора WHERE. В блоке FROM фрагмента предложения SELECT определяется производная таблица, формируемая модифицирующей командой с оператором OUTPUT. Для демонстрации такой возможности создадим таблицы Orders и OrdersAudit, выполнив сценарий, приведенный в листинге 1.

Предположим, нам нужно избавиться от заказов, размещенных до 2007 года, причем аудит удаления требуется только для заказов от клиентов A и D. Вместо двухэтапного процесса мы можем выполнить задачу в один заход с помощью компонуемого DML:

INSERT INTO dbo.OrdersAudit(orderid, orderdate, empid, custid, qty)
SELECT orderid, orderdate, empid, custid, qty
FROM (DELETE FROM dbo.Orders
OUTPUT deleted.*
WHERE orderdate < '20070101') AS D
WHERE custid IN ('A', 'D');

В таблице 1 показано содержимое таблицы OrdersAudit после выполнения такого запроса.

Обратите внимание, что текущая реализация компонуемого DML предусматривает для производных от DML таблиц только предложения INSERT SELECT с оператором WHERE. Другие синтаксические возможности во внешнем запросе не допускаются: это относится в том числе к группированию и объединениям. Кроме того, ограничения синтаксиса OUTPUT имеют силу и для компонуемого DML. То есть принимающая таблица может быть постоянной таблицей, временной таблицей или табличной переменной, но нельзя выводить результаты в табличное выражение (то есть представление) или таблицы с триггерами, несущими ограничения типа «первичный ключ — внешний ключ» или участвующие в репликации со слиянием или транзакционной репликации по подписке на обновление (в терминологии Books Online — «обновляемых подписках на репликацию транзакций»).

SQL Server 2008 поддерживает синтаксис OUTPUT и компонуемый DML в связке с новым оператором MERGE. Пример использования компонуемого DML с оператором MERGE выполняется сценарием из листинга 2. В этом примере показана возможность аудита строк, возвращаемых оператором MERGE, но только тех, которые потребовали выполнения операции INSERT; другие операции, изменившие строки таблиц при выполнении запроса, не будут отражены соответствующими строками в потоке вывода.

Декларирование и инициализация переменных

В SQL Server 2008 появилась новая возможность инициализации переменных сразу при их объявлении. Начальное значение переменной может быть любым самостоятельным выражением, которое возвращает скаляр. Например, в листинге 3 показано, как при объявлении переменных задаются начальные значения в виде констант, функций и возвращающих скаляр подзапросов. Таблица 2 содержит значения переменных после инициализации.

Составные операторы присваивания

Как и описанное выше нововведение, составные операторы присваивания — это еще одно небольшое усовершенствование синтаксиса. До перехода на SQL Server 2008, если требовалось внести значение в столбец, переменную или параметр, а само значение при этом было результатом работы оператора (например, сложения или вычитания) с принимающим столбцом и другим операндом, приходилось дважды указывать имя столбца в выражении. Например, для добавления 10 к значению в столбце col1 в команде UPDATE приходилось в блоке SET писать col1 = col1 + 10.

Составные операторы присваивания позволяют не повторять имя столбца-приемника, записывая операцию в виде col1 += 10. Поддержка этих операторов SQL Server 2008 распространяется на все случаи, где допустимо присваивание значений, такие как команды UPDATE, MERGE, SET и SELECT с присваиванием значения переменной. Составные операторы присваивания позволяют выполнять арифметические действия (+=, -=, *=, /=, %=), побитовые логические операции (|=, &=, ^=) и даже сцепление строк (+=). Следующий пример показывает, как используются разные составные операторы присваивания в контексте присвоения значений в предложении SELECT:

DECLARE
@a AS INT = 10, @b AS INT = 10, @c AS INT = 10, @d AS INT = 10, @e AS INT = 10,
@f AS INT = 10, @g AS INT = 10, @h AS INT = 10,
@i AS VARCHAR(10) = 'abc';
SELECT
@a += 2, @b -= 2, @c /= 2, @d *= 2, @e %= 2, — арифметика
@f |= 2, @g &= 2, @h ^= 2, — битовые операции
@i += 'def'; — сцепление строк
SELECT @a AS a, @b AS b, @c AS c, @d AS d, @e AS e, @f AS f, @g AS g, @h AS h, @i AS i;

Таблица 3 отражает значения переменных после присваивания с помощью составных операторов.

Зависимости в SQL

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

SQL Server 2008 регистрирует информацию о зависимостях, основываясь на ссылках по именам объектов. Таким образом, даже при создании объекта с зависимостью от несуществующего объекта SQL Server 2008 способен выполнить разбор определения и зарегистрировать именованные ссылки. Если при запросе информации о зависимости влияющего объекта не существует, SQL Server сможет отобразить имена влияющих объектов без идентификатора. Когда влияющий объект будет создан, впоследствии SQL Server сможет вернуть и его ID.

Отметим, что SQL Server 2008 не дает информации о зависимостях для динамически формируемого кода и кода общеязыковой среды исполнения. NET Common Language Runtime (CLR). Информация о зависимостях предоставляется только для ссылок, присутствующих в статичном коде: на объекты, привязанные и не привязанные к схемам, объекты в других базах данных или даже на других серверах.

Для получения информации о зависимостях можно выполнить код наподобие приведенного в листинге 4, который создает несколько тестовых объектов в базе данных tempdb. В SQL Server 2008 введены три объекта, возвращающих информацию о зависимостях: представление каталога sys.sql_expression_dependencies и функции динамического управления (DMF) sys.dm_sql_referenced_entities и sys.dm_sql_referencing_entities.

Представление sys.sql_expression_dependencies отображает зависимости с информацией об именах объектов и вытесняет представление sys.sql_dependencies, использовавшееся в предыдущих версиях. Например, следующий запрос отображает все зависимости в текущей базе данных:

SELECT
OBJECT_SCHEMA_NAME(referencing_id) AS srcschema,
OBJECT_NAME(referencing_id) AS srcname,
referencing_minor_id AS srcminorid,
referenced_id,
referenced_schema_name AS tgtschema,
referenced_entity_name AS tgtname,
referenced_minor_id AS tgtminorid
FROM sys.sql_expression_dependencies;

Этот запрос возвращает данные, показанные в таблице 4. Обратите внимание, что зависимости в динамическом коде SQL не были обнаружены, тогда как все зависимости, присутствующие в статическом коде, отражены. Отметим также, что зависимость Proc1 от несуществующей Proc3 была обнаружена и соответствующие имена отражены в таблице, но поскольку Proc3 не существует, ее атрибут referenced_id представлен значением NULL.

Функция динамического управления sys.dm_sql_referenced_entities возвращает все сущности, на которые ссылается переданная ей в качестве параметра сущность, то есть объекты, от которых переданная сущность зависит. Например, следующий код возвращает все объекты, от которых зависит dbo.V1:

SELECT
referenced_schema_name AS objschema,
referenced_entity_name AS objname,
referenced_minor_name AS minorname,
referenced_class_desc AS class
FROM sys.dm_sql_referenced_entities('dbo.V1', 'OBJECT');

Результат выполнения запроса показан в таблице 5 и свидетельствует о том, что dbo.V1 зависит от таблицы dbo.T1 и ее столбца dbo.T1.col1.

функция динамического управления sys.dm_sql_referencing_entities возвращает все сущности, которые ссылаются на переданную ей в качестве аргумента сущность, то есть все объекты, которые зависят от переданной сущности. Например, следующий запрос вернет все сущности, которые зависят от dbo.T1:

SELECT
referencing_schema_name AS objschema,
referencing_entity_name AS objname,
referencing_class_desc AS class
FROM sys.dm_sql_referencing_entities('dbo.T1', 'OBJECT');

Результаты выполнения показаны в таблице 6 и отражают тот факт, что от dbo.T1 зависят dbo.Proc2 и dbo.V1.

Небольшие изменения сильно упрощают жизнь

В SQL Server 2008 включено множество мелких доработок. Некоторые из них, такие как составные операторы присваивания и возможность инициализации переменных при объявлении, просто делают более удобным синтаксис и более кратким код. Однако ряд других изменений, например компонуемый DML и новая модель сбора информации о зависимостях объектов, более заметно влияют на производительность и стабильность систем.

SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
GO
INSERT INTO dbo.Orders
(orderid, orderdate, empid, custid, qty)
VALUES
(30001, '20060802', 3, 'A', 10),
(10001, '20061224', 1, 'A', 12),
(10005, '20061224', 1, 'B', 20),
(40001, '20070109', 4, 'A', 40),
(10006, '20070118', 1, 'C', 14),
(20001, '20070212', 2, 'B', 12),
(40005, '20080212', 4, 'A', 10),
(20002, '20080216', 2, 'C', 20),
(30003, '20080418', 3, 'B', 15),
(30004, '20060418', 3, 'C', 22),
(30007, '20060907', 3, 'D', 30);
IF OBJECT_ID('dbo.OrdersAudit', 'U') IS NOT NULL
DROP TABLE dbo.OrdersAudit;
CREATE TABLE dbo.OrdersAudit
(
auditlsn INT NOT NULL IDENTITY,
loginname SYSNAME NOT NULL DEFAULT (SUSER_SNAME()),
posttime DATETIME NOT NULL DEFAULT (CURRENT_TIMESTAMP),
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid VARCHAR(5) NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_OrdersAudit PRIMARY KEY(auditlsn)
);

INSERT INTO dbo.CustomersAudit(custid, companyname, phone, address)
SELECT custid, companyname, phone, address
FROM (MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
ON TGT.custid = SRC.custid
WHEN MATCHED THEN
UPDATE SET
TGT.companyname = SRC.companyname,
TGT.phone = SRC.phone,
TGT.address = SRC.address
WHEN NOT MATCHED THEN
INSERT (custid, companyname, phone, address)
VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
OUTPUT $action AS action,
inserted.custid,
inserted.companyname,
inserted.phone,
inserted.address) AS D
WHERE action = 'INSERT';

Листинг 3. Объявление и инициализация переменных

USE tempdb;
IF OBJECT_ID('dbo.Proc1', 'P') IS NOT NULL DROP PROC dbo.Proc1;
IF OBJECT_ID('dbo.Proc2', 'P') IS NOT NULL DROP PROC dbo.Proc1;
IF OBJECT_ID('dbo.V1', 'V') IS NOT NULL DROP VIEW dbo.V1;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
GO
CREATE PROC dbo.Proc1
AS
EXEC('EXEC dbo.Proc2');
EXEC dbo.Proc3;
GO
CREATE PROC dbo.Proc2
AS
SELECT * FROM dbo.T1;
GO
CREATE TABLE dbo.T1(col1 INT);
GO
CREATE VIEW dbo.V1
AS
SELECT col1 FROM dbo.T1;
GO