Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 271

This time, query optimizer has performed the seek operation only on the ProductDemo table
and didn't even get into checking anything in the parent table ProductModelDemo . Now
judge which execution plan looks better: before the foreign key constraint or after the foreign
key constraint.

How it works...

It is already said that DRI not only ensures the integrity of databases but also gains
performance benefits, and this is proved by the previous screenshot. By defining foreign key
field as NOT NULL in the child table, we are guiding the query optimizer that there is no value
in child field without reference of parent field and hence optimizer keep trust on this foreign
key and does not even go to look at the ProductModelDemo parent table to confirm this,
if no other data is requested from the parent table. This is the reason, the second SELECT
query performed seek operation on the child table only.

Apart from that, by defining the foreign key to the primary key field on the parent table, we also
ensure that no JOIN operation of a single row from the child table would bring two or more
rows from the parent table. Without it, optimizer would have to go to the parent table to check
whether we possibly have more than one corresponding row for the same row in the child table.

"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