"Trust" your foreign key to gain performance

We have already studied what DRI can do and by how much it can increase performance, in the previous recipe. This recipe is also related to DRI, which shows that even a single small option can play a big role as far as performance is concerned.

Getting ready

We are going to see the sys.foreign_keys system view to get information about the foreign key we defined in the table schema. Concentrate on the field is_not_trusted, in the view, which gives you information whether your foreign key is trusted or not.

How to do it...

Follow the steps given here to perform this recipe:

1. First of all, we have to create two tables for demonstration of this recipe from the AdventureWorks2012 database:

   IF OBJECT_ID('ProductDemo') IS NOT NULL
   DROP TABLE ProductDemo
   GO
   IF OBJECT_ID('ProductModelDemo') IS NOT NULL
   DROP TABLE ProductModelDemo
   GO
   select * into ProductModelDemo from Production.ProductModel
   select * into ProductDemo from Production.Product WHERE ProductModelID...