Esat Erkec
SQL Server foreign key and execution plan

Hidden secrets of SQL Server Foreign Keys

April 21, 2021 by

This article will describe the effect of SQL Server foreign keys on query plans which are created by the query optimizer.


Foreign keys are used to enforce the referential integrity between the data of the two tables and are frequently used in relational database designs. You can see the article, What is a foreign key in SQL Server to learn more details about the foreign keys.

Along with this referential connection duty of the foreign key, it influences the created execution plans because the query optimizer has the ability to use this referential integrity information when it creates the execution plans. So that, the optimizer can generate more efficient query plans.


At first, we will create a Customers table through the following query.

The following query will create the CustomerOrders table and the CustomerId column will be a foreign key for this table and it references the ID column of the Customers table.

SQL Server Foreign Keys and Join Elimination

For some cases, the query optimizer algorithm works pretty smartly so that it avoids redundant I/O operations and saves time for the query executions. The following methods are used by the query optimizer to improve query performances in the appropriate circumstances.

  • Contradiction Detection
  • Domain Simplification
  • Join Elimination
  • Row Goal

The SQL Server foreign keys create referential integrity between the data of the tables and the query optimizer can shape the execution plan using this referential integrity information. Join elimination is a technique that removes some unnecessary joins during the execution of the query so it decreases the I/O consumption and query execution time. Now, we will interpret the following query execution plan.

The execution plan of the query is very simple, the storage engine retrieves the data using indexes, and then this data is joined by nested loops join operator.

Execution plan of a query

Just now, we will remove the Customer tables column in the query and only retrieves the Amount and OrderData column data.

Result of a query

Now, we will analyze the executed query execution plan carefully because we will see that the query plan is formed differently because of the secondary key.

SQL Server foreign keys and join elimination interaction

One interesting point about this execution plan is that the storage engine only reads data from the CustomerOrders table and the nested loops and clustered index seek operators had been removed. The reason for this interesting situation is that the query optimizer knows that all of the CustomerID column data must be located in the ID column of the Customer table. At the same time, the executed query does not retrieve any data from the Customers table for this query optimizer for this the query optimizer thinks the join operation is redundant and removes the join operation. With this approach, the query optimizer eliminates unnecessary I/O operations and generates more efficient query plans. This method is called join elimination and generally join elimination method refers to foreign keys information.

SQL Server Foreign Keys: Trusted and Untrusted

After the bulk insert operations, check and foreign key constraints status are changed to not-trusted, if we don’t use the  CHECK_CONSTRAINTS parameter. It means that the bulk copied data is inserted into the destination table without checking the referential integrity. The main idea behind this approach is to increase the performance of the bulk copy operations. However, this situation prevents the optimizer from using the join elimination method. Let’s go through the details of this case with an example. Firstly, we will look at the trust status of the CustomerOrders table foreign constraint. The sys.foreignkeys system table can give metadata information of the SQL Server foreign keys.

Learning status of the foreign keys (trusted - non-trusted)

As seen in the above image, the status is_not_trusted is 0 so we can ensure that the referential integrity exists and we can not insert any row into the CustomerOrders table that not provides the foreign key data integrity. Now, we will disable this integrity by using the following query.

Learning details of the foreign keys to using sys.foreign_keys table

After disabling the check constraint, we will re-execute the same query which is the join elimination method performed by the query optimizer.

SQL Server foreign keys and non-trusted foreign keys interaction

The nested loops and clustered index scan operators are re-added to the query plan because of the disabled foreign key integrity. In this circumstance, the optimizer can not ensure the foreign key decided to read data from the Customer table. As a result, we can say that untrusted foreign keys ignore to use join elimination method.

SQL Server Foreign Keys with an Insert Statement

As we stated in the entrance part of the article, the foreign keys affect the query optimizer behaves and this situation is also valid for the insert statements. In the insert operations, SQL Server checks the inserted child row that exists in the parent table to verify the referential integrity. When a foreign key status is not trusted then the storage engine does not require to check the inserted foreign key is exists in the parent table. The execution plan of the following query will only include clustered index insert operator because of this situation.

SQL Server insert statement and foreign key interaction

Now, we will re-enable the foreign key constraint and will change its status as trusted.

Enabling the foreign key

After enabling the foreign key, we will insert a new row to the table and look at the interesting changes in its query plan.

SQL Server foreign key execution plan

Before start talking about this new and interesting execution plan, we will briefly touch on the nested loops operator details. The nested loops operator is used to join data of the two tables and its working method is like to nested loop algorithm pattern that is used in modern programming languages. In this algorithm, a loop is placed inside of another loop. The nested loops operator makes the same thing and it takes a row from the outer table and starts to search this row in the inner table. This process continues until the nested loops operator consumes all rows of the outer table. When we look at the properties of the nested loops operator, we see that it performs a left semi join operation. At the same time, the Outer References attribute shows which column data is searched in the inner table.

Nested loop operator

The clustered index insert is performed on the outer table (CustomerOrders) for each CustomerID row an index seeks operation is performed. This case is shown in the seek predicate property of the clustered index seek operator.

Clustered index seek and SQL Server foreign key

The assert operator is used to verify conditions execution of the queries and its Predicate attribute shows which condition is evaluated by this operator.

Assert operator

Let’s explain the meaning of this predicate attribute of the assert operator. If the output of the nested loop returns a null value, the assert operator evaluates it as 0 and the query returns an error otherwise the insert operation will be performed successfully. So the insert operation is aborted and the transaction is rollbacked.

Put all the pieces together

The insert statement execution plan of the queries which involve foreign keys might be a bit complicated. For this reason, we will briefly interpret the execution plan again.

SQL Server foreign key and execution plan

As a first step, the insert operation is completed and then the inserted foreign key value is checked for existence in the parent table. The nested loops join operator pass this result to the assert operator. The result of the assert operator reveals that the query will either pass or fail.


In this article, we have deep-dived into the interaction between SQL Server foreign keys and execution plans with all aspects. As we learned, foreign keys are widely used in database designs, and learning their execution plan can help to understand their performance of the queries. On the other hand, we learned trusted and un-trusted foreign keys can change the same query execution plans.

Esat Erkec