The result of the assert operator reveals that the query will either pass or fail. The nested loops join operator pass this result to the assert operator. Reason, we will briefly interpret the execution plan again.Īs a first step, the insert operation is completed and then the inserted foreign key value is checked for existence in the parent table. The insert statement execution plan of the queries which involve foreign keys might be a bit complicated. So the insert operation is aborted and the transaction is rollbacked. 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. Let’s explain the meaning of this predicate attribute of the assert operator. The assert operator is used to verify conditions execution of the queries and its PredicateĪttribute shows which condition is evaluated by this operator. This case is shown in the seek predicate property of the clustered index seek operator. The clustered index insert is performed on the outer table (CustomerOrders) for each CustomerID row an index seeks At the same time, the Outer References attribute shows which column data is searched in the inner table. When we look at the properties of the nested loops operator, we see that it performs a left semi join operation. This process continues until the nested loops operator consumes all rows of the outer table. 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. In this algorithm, a loop is placed inside of another loop. 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. VALUES ( 5, CAST ( '2' AS DATETIME ), 3, 47.01 )īefore start talking about this new and interesting execution plan, we will briefly touch on the nested loops operator details. TheĮxecution plan of the following query will only include clustered index insert operator because of this situation.

Then the storage engine does not require to check the inserted foreign key is exists in the parent table. Row that exists in the parent table to verify the referential integrity. In the insert operations, SQL Server checks the inserted child Situation is also valid for the insert statements. SQL Server Foreign Keys with an Insert StatementĪs we stated in the entrance part of the article, the foreign keys affect the query optimizer behaves and this As a result, we can say that untrusted foreign keys ignore to use join elimination method. In this circumstance, the optimizer can not ensure the foreign key decided to read data from the Customer table. The nested loops and clustered index scan operators are re-added to the query plan because of the disabled foreign key integrity. OBJECT_NAME ( parent_object_id ) = 'CustomerOrders'Īfter disabling the check constraint, we will re-execute the same query which is the join elimination method The sys.foreignkeys system table can give metadata information of the SQL Server foreign keys. Firstly, we will look at the trust status of the CustomerOrders table foreign constraint. Let’s go through the details of this case with an example.

However, this situation prevents the optimizer from using the join elimination method. The main idea behind this approach is to increase the performance of the bulk copy operations. It means that the bulk copied data is inserted into the destination table without checking the referential integrity. SQL Server Foreign Keys: Trusted and UntrustedĪfter the bulk insert operations, check and foreign key constraints status are changed to not-trusted, if we don’t use the CHECK_CONSTRAINTS parameter. This method is called join elimination and generally join elimination method refers to foreign keys information. With this approach, the query optimizer eliminates unnecessary I/O operations and generates more efficient query plans. 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. 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. 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. Now, we will analyze the executed query execution plan carefully because we will see that the query plan is formedĭifferently because of the secondary key.

Sqlite foreign key insert statements