12

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?

0
14

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.

enter image description here

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.

enter image description here

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.

0
4

You are losing query optimizations. In practice the only optimization that I recall is elimination of redundant joins. For example if you have in a view:

select *
from Orders o
join Customers c on o.CustomerID = c.ID

And when using the view you do not make use of the columns of c then that join can be deleted if there is a proper FK set up.

Your EXISTS example is a special case of removing a redundant join. I don't think that particular example is practically relevant.

You also lose the strict data integrity that a trusted constraint provides.

3

The NOCHECK option does exactly what it says on the tin.

It's mainly used for adding a foreign key on half way through the existence of a table where there's a new relationship that may not have been required (that's my understanding at least).

It means that the column that has the foreign key MAY have values within it that do not correlate to the appointed value that it should relate to.

This means that when you have a NOCHECK option on SQL Server has to actually go and check if that key value is actually a primary key. If NOCHECK is not set then SQL Server presumes that anything that is in that column definitely exists because the entry could not exist in the table if it was not already a primary key, and you couldn't delete the primary key without deleting the row in question.

Simply NOCHECK is a foreign key that you can't trust to actually relate to anything.

You're not actually losing anything other than the trust that the primary key guaranteed to be there.

1
  • 1
    It's not clear from your answer whether there is any enforcement of the foreign key after initial creation of the constraint. Could you clarify what happens if you try to INSERT a new row that relates to a non-existing parent row or if you try to DELETE a row that has child rows later on?
    – jpmc26
    Sep 8 '15 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.