"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') ...

Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial