Заметки Дмитрия Пилюгина о Microsoft SQL Server 

PDF

Если в таблице создано ограничение внешнего ключа (Foreign Key Constraint), то при добвлении данных в эту таблицу будет проверяться наличие/отсутствие соответствующих записей в «родительской» таблице.
Иногда, если загрузка данных идет из, условно говоря, доверенного источника (имеется ввиду нам известно, что все данные из источника отвечают требованиям ссылочной целостности), для ускорения загрузки ограничения внешних ключей отключают, а после загрузки — включают обратно.
Смоделируем такую ситуацию и посмотрим, что будет.

use tempdb
go
-- создадим тестовые таблички
create table dbo.ATable ( ATableID int not null primary key )
create table dbo.BTable (
	BTableID int not null primary key,
	ATableID int not null,
	constraint FK_ATable_ATableID foreign key(ATableID) references dbo.ATable(ATableID)
)
go
-- заполним данными
insert into dbo.ATable(ATableID) select 1
insert into dbo.BTable(BTableID, ATableID) select 1,1
go
-- выполним запрос с просмотром плана выполнения
set showplan_xml on
go
select
	count(*)
from
	dbo.BTable b
where
	exists (select * from dbo.ATable a where a.ATableID = b.ATableID)
go
set showplan_xml off
go

Видим, что за счет ограничения внешнего ключа и того, что колонка BTable.ATableID не может быть null, оптимизатор полностью исключил из плана обращение к таблице ATable.
Теперь отключаем ограничение, эмулируем загрузку и включаем ограничение.

-- отключаем
alter table dbo.BTable nocheck constraint FK_ATable_ATableID
go
-- загружаем
insert into dbo.BTable(BTableID, ATableID) select 2,1
go
-- включаем
alter table dbo.BTable check constraint FK_ATable_ATableID
go
--Выполним тот же самый запрос и посмотрим план
set showplan_xml on
go
select
	count(*)
from
	dbo.BTable b
where
	exists (select * from dbo.ATable a where a.ATableID = b.ATableID)
go
set showplan_xml off
go

Что мы видим, так это то, что план изменился, теперь 50% ресурсов занимает скан таблицы ATable.
Почему так произошло, ведь мы вроде вернули систему в исходное состояние до загрузки. На самом деле не совсем так. О том, что источник был «доверенный» знали только мы, ну а сервер нам не доверяет и правильно делает. =)
Как именно он нам не доверяет, можно посмотреть сделав такой запрос.

select is_not_trusted from sys.foreign_keys where name = 'FK_ATable_ATableID'

В момент первого выполнения запроса этот признак был равен 0.

Дело в том, что после загрузки данных, при включении ограничения у сервера нет уверенности в том, что в таблицу не попали записи, не имеющие соответствующих записей в родительской таблице.
«Убедить» сервер в обратном можно заставив его принудительно проверить данные в таблице. Для этого при включении ограничения нужно указать опцию with check.

alter table dbo.BTable with check check constraint FK_ATable_ATableID
go
set showplan_xml on
go
select
	count(*)
from
	dbo.BTable b
where
	exists (select * from dbo.ATable a where a.ATableID = b.ATableID)
go
set showplan_xml off
go

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

-- Удаляем тестовые таблички.
drop table BTable
drop table ATable
go

PDF