What is lost when I create a Foreign Key using `WITH NOCHECK`?

I know that if I do an EXISTS() call on a FK lookup value, then, if that FK constraint is trusted, the result is immediate.

And if it is not trusted (like when I create the FK using WITH NOCHECK) then SQL Server has to go and check to table to see if the value is actually there.

Is there anything else I lose by using NOCHECK?

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,
  Name varchar(50),
  IsBig bit not null,
  CountryID int not null
);

alter table dbo.City 
  add constraint FK_CountryID 
  foreign key (CountryID) 
  references dbo.Country(CountryID);

There are not so many countries but a gazillion of cities and some of them are big cities.

Sample data:

-- Three countries
insert into dbo.Country(CountryID, Name) values
(1, 'Sweden'),
(2, 'Norway'),
(3, 'Denmark');

-- 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
from dbo.Country
  cross apply (
              select top(100) *
              from sys.columns
              ) 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
as
select count_big(*) as BigCityCount,
       City.CountryID,
       Country.Name as CountryName
from dbo.City
  inner join dbo.Country
    on City.CountryID = Country.CountryID
where City.IsBig = 1 
group by City.CountryID,
         Country.Name;

 go

create unique clustered index CX_BigCityCount
  on dbo.BigCityCount(CountryID);

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 Country table.

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 City.

When the key is trusted, SQL Server knows there can be no rows in City that would match the new row in Country.

StackExchange Database Administrators Q#114411, answer score: 14