Основные сведения о зависимостях SQL

Зависимости SQL представляют собой именные ссылки, используемые в выражениях SQL и делают одну определяемую пользователем сущность зависимой от другой. Если сущность ссылается на другую сущность в своем определении, которое хранится в системном каталоге, то такая сущность называется ссылающейся сущностью. Сущность, на которую ссылается другая сущность, называется упоминаемой сущностью. Компонент Database Engine отслеживает два типа зависимостей.

  • Зависимость, привязанная к схеме

    Привязанная к схеме зависимость — это зависимость между двумя сущностями, которая не допускает удаления или изменения упоминаемой сущности, пока существует ссылающаяся сущность. Привязанная к схеме зависимость создается в тот момент, когда с помощью предложения WITH SCHEMABINDING создается представление или определяемая пользователем функция. Привязанная к схеме зависимость также может быть создана, когда таблица ссылается на другую сущность (например определяемую пользователем функцию Transact-SQL, определяемый пользователем тип или коллекцию XML-схем) в ограничении CHECK или DEFAULT либо в определении вычисляемого столбца. Указание объекта с помощью двухкомпонентного имени (например имя_схемы.имя_объекта), не считается привязанной к схеме зависимостью.

  • Зависимость без привязки к схеме

    Не привязанная к схеме зависимость — это связь между двумя сущностями, которая не препятствует удалению или изменению упоминаемой сущности.

На следующем рисунке показан пример SQL зависимости.

Иллюстрация зависимости в SQL

На рисунке представлены две сущности: процедура X и процедура Y. Процедура Х содержит выражение SQL, имеющее именную ссылку на процедуру Y. Процедура Х называется ссылающейся сущностью, а процедура Y — упоминаемой сущностью. Так как процедура Х зависит от процедуры Y, то процедура Х завершится ошибкой времени выполнения, если процедура Y не существует. Тем не менее процедура Y не завершится ошибкой, если процедура Х не существует.

Следующий пример показывает, как хранимая процедура X может зависеть от хранимой процедуры Y.

USE tempdb;
GO
CREATE PROCEDURE dbo.Y AS
SELECT * FROM sys.objects
GO
CREATE PROCEDURE dbo.X as
    EXEC dbo.Y;
GO

Чтобы просмотреть зависимость X от Y, запустите следующий запрос:

SELECT * 
FROM sys.sql_expression_dependencies 
WHERE referencing_id = OBJECT_ID('X')
    AND referenced_id = OBJECT_ID('Y')
    AND referenced_schema_name = 'dbo'
    AND referenced_entity_name = 'Y'
    AND referenced_database_name IS NULL
    AND referenced_server_name IS NULL;
GO

Типы ссылающихся и упоминаемых сущностей

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

Тип сущности

Ссылающаяся сущность

Упоминаемая сущность

Таблица

Да*

Да

Представление

Да

Да

Хранимая процедура Transact-SQL**

Да

Да

Хранимая процедура CLR

Нет

Да

Определяемая пользователем функция Transact-SQL

Да

Да

Определяемая пользователем функция среды CLR

Нет

Да

Триггер CLR (DML и DDL)

Нет

Нет

Триггер DML Transact-SQL

Да

Нет

Триггер DDL Transact-SQL уровня базы данных

Да

Нет

Триггер DDL Transact-SQL уровня сервера

Да

Нет

Расширенные хранимые процедуры

Нет

Да

Очередь

Нет

Да

Синоним

Нет

Да

Тип (псевдоним и определяемый пользователем тип данных CLR)

Нет

Да

Коллекция схем XML

Нет

Да

Функция секционирования

Нет

Да

* Таблица отслеживается в качестве ссылающейся сущности, только если она ссылается на модуль Transact-SQL, определяемый пользователем тип или коллекцию XML-схем в определении вычисляемого столбца, ограничении CHECK или ограничении DEFAULT.

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

Как отслеживается информация о зависимостях

Компонент Database Engine автоматически отслеживает информацию о зависимостях при создании, изменении или удалении ссылающихся сущностей и записывает ее в системный каталог SQL Server. Например, при создании триггера, который ссылается на таблицу, регистрируется зависимость между этими двумя сущностями. Если в дальнейшем триггер будет удален, то информация о зависимости также будет удалена из системного каталога.

В отличие от более ранних версий SQL Server, в которых зависимости отслеживались по идентификатору, в текущей версии зависимости отслеживаются по имени. Это означает, что компонент Database Engine отслеживает информацию о зависимости между двумя сущностями даже в том случае, если упоминаемая сущность не существует на момент создания ссылающейся сущности. Такое стечение обстоятельств может быть вызвано отложенной интерпретацией имен. Например, хранимую процедуру, ссылающуюся на таблицу, можно создать (но не выполнить), даже если упоминаемая таблица не существует в базе данных. Компонент Database Engine регистрирует зависимость между процедурой и таблицей, однако идентификатор таблицы зарегистрировать нельзя, поскольку данный объект еще не существует. Если впоследствии такая таблица создается, то ее идентификатор возвращается вместе с другой информацией о зависимости.

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

  • С помощью любой из следующих инструкций в определении модуляTransact-SQL:

    • инструкции языка обработки данных (SELECT, INSERT, UPDATE или DELETE);

    • EXECUTE

    • DECLARE

    • SET (если используется совместно с определяемой пользователем функцией или определяемым пользователем типом. Например, DECLARE @var int; SET @var = dbo.udf1).

    Не отслеживаются сущности, упомянутые в модуле Transact-SQL с помощью инструкций языка DDL CREATE, ALTER или DROP.

  • С помощью инструкций CREATE, ALTER или DROP TABLE, в случае, если инструкции не присутствуют в модуле Transact-SQL, а упоминаемая сущность — это определяемая пользователем функция Transact-SQL, определяемый пользователем тип, коллекция XML-схем, заданная в вычисляемом столбце, либо ограничения CHECK или DEFAULT.

Межбазовые и межсерверные зависимости

Межбазовая зависимость создается, когда некоторая сущность ссылается на другую сущность с помощью допустимого трехкомпонентного имени. Межсерверная зависимость создается, когда некоторая сущность ссылается на другую сущность с помощью допустимого четырехкомпонентного имени. Имена сервера и базы данных записываются только в том случае, если они указываются явно. Например, если имя таблицы задается в виде MyServer.MyDB.MySchema.MyTable, записываются имена сервера и базы данных, но если имя таблицы задается в виде MyServer..MySchema.MyTable, записывается только имя сервера. Сведения о допустимых многокомпонентных именах см. в разделе Синтаксические обозначения в Transact-SQL (Transact-SQL).

Действуют следующие ограничения.

  • Межсерверные зависимости инструкций OPENROWSET, OPENQUERY и OPENDATASOURCE не отслеживаются.

  • Зависимости для инструкции EXEC ('…') AT linked_server не отслеживаются.

В следующей таблице содержатся отслеживаемые межсерверные и межбазовые зависимости, а также информация, записываемая в системный каталог и сообщаемая представлением Представление каталога sys.sql_expression_dependencies (Transact-SQL).

Выражение SQL в модуле

Отслеживается

Имя упоминаемого сервера

Имя упоминаемой базы данных

Имя упоминаемой схемы

Имя упоминаемой сущности

SELECT * FROM s1.db2.sales.t1

Да

s1

db2

sales

t1

SELECT * FROM db3..t1

Да

 

db3

 

t1

EXEC db2.dbo.Proc1

Да

 

db2

dbo

proc1

EXEC ('…') AT linked_srv1

Нет

 

 

 

 

EXEC linked_svr1.db2.sales.proc2

Да

linked_svr1

db2

sales

proc2

Влияние параметров сортировки на отслеживание зависимостей

Параметры сортировки определяют правила, использующиеся при сортировке и сравнении данных. Параметры сортировки базы данных используются для идентификации информации о зависимости сущностей в базе данных. Например, если хранимая процедура ссылается на две сущности — Some_Table и SOME_TABLE, которые находятся в базе данных, использующей параметры сортировки с учетом регистра, то будет зарегистрирована информация о двух сущностях, поскольку сравнение двух имен покажет, что они не идентичны. Однако если в базе данных используются параметры сортировки, не учитывающие регистр, то зарегистрирована будет только одна зависимость.

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

Рекомендуется следующее определение хранимой процедуры. Если хранимая процедура создается в базе данных с параметрами сортировки с учетом регистра, которая находится в экземпляре SQL Server с параметрами сортировки сервера, не учитывающими регистр, то для сущностей srv_referenced.db_referenced.dbo.p_referenced и srv_referenced.db_referenced.DBO.P_REFERENCED будут зарегистрированы две зависимости.

CREATE PROCEDURE p_referencing AS
    EXECUTE srv_referenced.db_referenced.dbo.p_referenced
    EXECUTE srv_referenced.db_referenced.DBO.P_REFERENCED
    EXECUTE SRV_REFERENCED.DB_REFERENCED.dbo.p_referenced;

Разрешение неоднозначных ссылок

Ссылка является неоднозначной, если на этапе выполнения она может разрешиться к определяемой пользователем функции, определяемому пользователем типу или ссылке XQuery на столбец типа xml.

Рекомендуется следующее определение хранимой процедуры.

CREATE PROCEDURE dbo.p1 AS 
    SELECT column_a, Sales.GetOrder() FROM Sales.MySales; 

Во время создания хранимой процедуры неизвестно, является ли ссылка Sales.GetOrder() определяемой пользователем функцией GetOrder в схеме Sales или столбцом Sales определяемого пользователем типа с методом GetOrder(). Если ссылка является неоднозначной, то зависимость отмечается как неоднозначная путем присвоения столбцу is_ambiguous в представлениях sys.sql_expression_dependencies и sys.dm_sql_referenced_entities значения 1. Возвращается следующая информация о зависимостях.

  • Зависимость между хранимой процедурой и таблицей.

  • Зависимость между хранимой процедурой и определяемой пользователем функцией. Если функция существует, указывается ее идентификатор; иначе значение идентификатора равно значению NULL.

  • Зависимость от функции отмечается как неоднозначная. То есть столбцу is_ambiguous присваивается значение 1.

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

Поддержание зависимостей

Компонент Database Engine поддерживает как связанные, так и не связанные со схемами зависимости. Эти зависимости автоматически обновляются во время любой операции, влияющей на отслеживание зависимостей, например при обновлении базы данных более ранней версии SQL Server или при изменении параметров сортировки базы данных.