In last two blog posts of this series Part 8 and Part 9 I’ve explained SQL Server execution plan using INSERT, UPDATE statements. You guessed it right; today we would cover kind of execution plan created using DELETE statement.
Let’s run following code and find out the execution plan;
Yes, the plan is quite big but we shall cover them one by one wherever required. Deleting a row or a set of rows is always related to the underlying table as we can understand, that to say any tables related to the primary key of the table on which we are removing data needs to be checked to ensure integrity of data.
Ok, let us start reading the plan on the right and top to bottom; at first we see a Clustered Index Delete operator. When you look at the seek predicate (image below) to DELETE operation is as following;
Prefix: [AdventureWorks2008R2].[Person].[Address].AddressID = Scalar Operator-(CONVERT_IMPLICIT(int, [@1],0))
In essence this means that, a parameter @1 was used to lookup AddressID whereas we didn’t use any parameter in our code and have queried with AddressID = 50. This is an indication that query engine is generating a reusable plan as per the rules of simple parameterization.
After this there are Non-Clustered Index Seek, Clustered Index Scan combined with Nested Loop Joins. These Nested Loop Joins are Left Semi Joins. These operators will return a value if the join predicate between two tables matches or in case no join predicate supplied and each one returns value.
Going further left, there is an Assert operator which helps to check referential integrity from the values returned from each Join, all the tables related to the table from which we’re attempting to delete data. If there aren’t any, DELETE is completed and if they return any value and error is generated and DELETE operation aborted like following;
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint “FK_BusinessEntityAddress_Address_AddressID”. The conflict occurred in database “AdventureWorks2008R2”, table “Person.BusinessEntityAddress”, column ‘AddressID’.
The statement has been terminated.
At the end we see the generic T-SQL Language Element Catchall operator (image below), which notifies us a DELETE operation.
I hope this explains DELETE operation plan. I will conclude this series on my next post, see you soon.