Michael J. Swart

It’s always a good idea to keep your constraints trusted. You can check to see which constraints in your database are not trusted with these commands:

select fk.name from sys.foreign_keys fk where fk.is_not_trusted = 1
select cc.name from sys.check_constraints cc where cc.is_not_trusted = 1

Constraints can become untrusted after a bulk load operation is performed without the CHECK CONSTRAINTS option. Foreign keys can be untrusted if they were defined with the NOT FOR REPLICATION option.

The obvious reason for keeping constraints trusted is that it means SQL Server is enforcing the data integrity defined by these constraints.

But there’s also a more subtle performance gain that I want to demonstrate below. If SQL Server can trust constraints, then sometimes it can decide it doesn’t need to do a join. Such queries are rare, but I’ve seen them pop up.

The following example works against the MS’s Adventureworks sample database. The example below uses two tables, Sales.Customers and Sales.SalesOrderHeader. There is a foreign key making sure that every SalesOrderHeader record references one Customer record.

use AdventureWorks
GO

-- the inner join in the following query has no effect here and sql server 
-- eliminates the join from the query plan. It's because the foreign 
-- key relationship that SQL Server can trust and simplify the query plan.
-- It only has to count orders.
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID
GO

-- Drop the foreign key and sql server can no longer simplify.
-- It can't guarantee that every order has a customer.
-- Again it has to join with customers.
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID];
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID;
GO

-- Create the key and try again.
ALTER TABLE [Sales].[SalesOrderHeader]  
ADD CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] 
    FOREIGN KEY([CustomerID])
    REFERENCES [Sales].[Customer] ([CustomerID])
    NOT FOR REPLICATION;
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID;
-- hmmm the foreign key is back in force, but we're still joining with customers.
-- It's because the foreign key is not trusted (related to the not for replication clause)
select fk.is_not_trusted from sys.foreign_keys fk where fk.name = 'FK_SalesOrderHeader_Customer_CustomerID'
GO

-- let's try again.
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID];
ALTER TABLE [Sales].[SalesOrderHeader]  
ADD CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] 
    FOREIGN KEY([CustomerID])
    REFERENCES [Sales].[Customer] ([CustomerID]);
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID;
GO
-- much better