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

How it works...
In both the previous examples (first query of both examples), query optimizer was not able to
use the proper non-clustered index that we had defined on the respective column due to the
functions DATEPART and LEFT , used with column name in the WHERE clause. At the same
time the second query, in both examples, has used an alternate way to achieve the same
result set, which is why query optimizer has used the Index Seek operation on the indexes
defined on predicate columns.
Improving performance by Declarative
Referential Integrity (DRI)
Declarative Referential Integrity (DRI) ensures integrity of the database by a properly
managed primary key and foreign key relationship. Correctly defined primary keys and
foreign keys help query optimizer to select the best-suited execution plan for the query.
It has been observed many times that developers create a master table with a primary key
and use that primary key field in the child table but don't define a foreign key in the table
schema. This is not a good practice because by initiating a foreign key in the child table, you
ensure that each record in the child table has a reference key in the parent table. This is a
good thing, right? But, let us clarify that, by maintaining the parent and foreign key properly,
we not only achieve integrity in the database but are also able to gain performance benefits.
We have an example to prove it.