SQL Server insert execution secrets (on) one of the simplest insert Analysis

This article uses SQL Server 2005

Preparations:
I need a user table. This table has several fields and a self-increasing number ID. The table structure is as follows:

This is a much simpler user table than in real-world applications, and userid is a self-increasing field.

Start executing the simplest insert:

 
Insert into [db_bj]. [DBO]. [user] ([username], [email], [Signature], [createtime], [IP]) values ('yukaizhao', 'yukaizhao @ abc.com ', 'My blog is http://www.cnblogs.com/yukaizhao/', '2017-06-01 ', '2017. 0.0.1 ')
 

In SQL Server Manager, select the icon () in the toolbar to display the execution plan and execute the statement:

The execution plan is as follows:

The query plan shows that the SQL statement executed by SQL Server is not the original SQL statement we wrote. SQL Server first intelligently converts our SQL statement into a parameter SQL statement, as shown below:

 
Insert into [db_bj]. [DBO]. [user] ([username], [email], [Signature], [createtime], [IP]) values (@ 1, @ 2, @ 3, @ 4, @ 5)
 

After this step, SQL server enables SQL statements with different parameters to use the same pre-compiled SQL statement.

We will look at the execution process of the insert statement from each step of the execution plan. Note that the execution plan of SQL server should be viewed from right to left.

1. Constant scanning is used to generate a row based on constants in the SQL statement entered by the user.
The original words of msdn are as follows:
"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"

The above is an explanation on msdn. Constant scanning will introduce one or more constant rows to a query. Generally, constant scanning is followed by scalar operators, the calculated scalar operator adds columns to the rows produced by the constant scan operator.

2. calculate the scalar. In this step, the auto-increment id value is generated. Please see, this step outputs an expression expr1003. Why is this output value auto-increment ID? Please refer to the figure in 4th.
 
3. The second calculates the scalar and assigns the constant value in the original SQL to five variables.
4. Insert clustered indexes, perform the insert operation, and add data to the data table. See:
 
The first red box in the figure is the expression assigned to username, [db_bj]. [DBO]. [user]. [username] = raiseifnull ([expr1004]). db_bj In This predicate is the name of my test database, and raiseifnull should be a function automatically added by SQL Server, if the parameter value is null, an exception is thrown. However, this function cannot be called by users.
The second red box is used to assign values to userid. Here we can confirm our inference in step 1.

5. The last step is insert, which stores the execution plan of the input SQL statement for reuse.

This is only the analysis of the simplest insert statement. Next we will continue to analyze a slightly more complex insert statement.

related essays: SQL Server insert execution secrets (lower) insert analysis with foreign keys