Dear Friends,
SQL Server engine generates SQL Server execution plan for all SQL queries in order to figure out best path of execution when we submit any request. In my previous blogs, I’ve covered SELECT statements; today we will take a look at execution plan for INSERT statement.
For simplicity, I picked up Person.[Address] table. Following is the insert statement that we are going to analyze today;
Reading execution plan from right a new operator Constant Scan the insert statement starts with. As per MSDN ‘The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used after a Constant Scan to add columns to a row produced by the Constant Scan operator’.
In our query, it is building a row in order for the next two Compute Scalar operators to have a place to add output. There are two Compute Scalar operators that are appearing in our INSERT statement. First of them, calls getidentity which is the exact moment when the identity value is generated for the row to be inserted. This is the first thing that is done in the plan.
Next Compute Scalar operation occurs which outputs a series of placeholders for the rest of the data to be inserted and creates the new uniqueidentifier value and the date and time using the function GETDATE(). These are then passed to Clustered Index Insert which holds the majority of the cost of this plan. Output value of this INSERT statement Person.Address.StateProvinceId is passed to the Nested Loop Join which received another input from Clustered Index Seek against Person.StateProvince table.
In simple words, there was a read during INSERT to check for the referential integrity on the FK of StateProvinceId. Once checked, join outputs new expression which is tested by Assert Operator which verifies that a particular condition exist or not. Here in our case it checked if Expr1015 equals to zero i.e. that the data that was attempted to be inserted into the Address.StateProvinceId field matched a chunk of data in the Person.StateProvince table part of the referential integrity check.
Stay tuned, I’ll be back soon with the next one on this series.
Regards
Kanchan Bhattacharyya
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
