As you have discovered with your
exists example, SQL Server can use the fact that a foreign key is trusted when the query plan is built.
Is there anything else I lose by using NOCHECK?
Apart from the fact that you can add values to a column that should not be there as answered by Ste Bov you will have more scenarios where the query plan will be better when the foreign key is trusted.
Here is one example with an indexed view.
You have two tables with a trusted FK constraint.
create table dbo.Country
CountryID int primary key,
Name varchar(50) not null
create table dbo.City
CityID int identity primary key,
IsBig bit not null,
CountryID int not null
alter table dbo.City
add constraint FK_CountryID
foreign key (CountryID)
There are not so many countries but a gazillion of cities and some of them are big cities.
-- Three countries
insert into dbo.Country(CountryID, Name) values
-- Five big cities
insert into dbo.City(Name, IsBig, CountryID) values
('Stockholm', 1, 1),
('Gothenburg', 1, 1),
('Malmoe', 1, 1),
('Oslo', 1, 2),
('Copenhagen', 1, 3);
-- 300 small cities
insert into dbo.City(Name, IsBig, CountryID)
select 'NoName', 0, Country.CountryID
cross apply (
select top(100) *
) as T;
The most often executed queries in this application is related to finding the number of big cities per country. To speed things up with that we add an indexed view.
create view dbo.BigCityCount with schemabinding
select count_big(*) as BigCityCount,
Country.Name as CountryName
inner join dbo.Country
on City.CountryID = Country.CountryID
where City.IsBig = 1
group by City.CountryID,
create unique clustered index CX_BigCityCount
After a while comes a demand of adding a new country
insert into dbo.Country(CountryID, Name) values(4, 'Finland');
The query plan for that insert has no surprises.
A clustered index insert to the
Now, if your foreign key was not trusted
alter table dbo.City nocheck constraint FK_CountryID;
and you add a new country
insert into dbo.Country(CountryID, Name) values(5, 'Iceland');
you would end up with this not so pretty picture.
The lower branch is there to update the indexed view. It does a full table scan of
City to figure out if the country with
CountryID = 5 already has rows in the table
When the key is trusted, SQL Server knows there can be no rows in
City that would match the new row in