Learn how to read SQL Server execution Plan (iii)--list Query Chapter

First, Nested loops

-- ------------------Nested Loops-------------------- /* the icon for looping nested connections is also very image, in the above external input (Outer input), here is the clustered index scan. And in the following internal input (Inner input), here is the clustered index lookup. The external input is executed only once, and the internal input is looked up based on each row of the external input that satisfies the join condition. This is done 7 times for the internal input because it is 7 rows. According to the principle of nested loops it is not difficult to see that because the external input is scanned, the internal input is lookup, when the two join table outside the input result set is relatively small, and the internal input to find the table is very large, the query optimizer prefers to choose the loop nesting method.  */SELECT* from as uINNERJOIN  as C  on = C.userid

Second, merge connection

--------------------Merge Connections--------------------/*Unlike loop nesting, a merge connection is a single access from each table. From this point of view, merging connections is much faster than looping nesting. From the principle of merging joins it is not difficult to imagine that merging connections first requires both parties to be orderly. And the condition of the join is equal to the number. Since two input conditions are already in order, it is not difficult to see why the merge join is only allowed to be equal to the join after a row is compared from each input set, equal returns, and unequal discards. We can see this principle from the icon in Figure 11. If both sides of the input data are unordered, the Query Analyzer does not select the merge connection, and we can also force the merge connection with the index hint, in order to achieve this, the execution plan must be ordered with a sort step. This is why the SQL statement above is an option (MERGE join). The ColumnID column of the article table is sorted above. */SELECT *  fromDbo. UserInfo asuINNER JOINDbo. Coupon asC onU.id=C.useridOPTION(MERGEJOIN)

Third, hash connection

--------------------Hash Connection--------------------/*Hash joins also only need to access data from both sides of the 1 times. Hash connections are implemented by establishing a hash table in memory. This consumes more memory and consumes tempdb if memory is low. But it's not like merging connections that requires both sides to be orderly. Delete the primary key of the userinfo and the clustered index in it, and if you execute the following SQL to remove the clustered index, two sequential input SQL Server chooses a lower-cost merge connection. SQL Server uses two of the above inputs to generate a hash table, the following input to probe, you can see the information in the Properties window, usually, the data is found in one or both sides of the condition is not sorted, the hash match is chosen. */ALTER TABLEDbo. UserInfoDROP CONSTRAINTpk_userinfo_id--Delete primary Key--DROP Index Index_userinfo_name--delete the clustered index--ALTER TABLE dbo. UserInfo ADD CONSTRAINT pk_userinfo_id PRIMARY key CLUSTERED (Id)--Create primary keySELECT *  fromDbo. UserInfo asuINNER JOINDbo. Coupon asC onU.id=C.userid

Four, multi-table parallel

--------------------Multi-table parallel--------------------/*When multiple tables are connected, SQL Server also allows query parallelism in multi-CPU or multi-core scenarios, which undoubtedly improves efficiency. */SELECT *  fromDbo. UserInfo asuINNER JOINDbo. Coupon asC onU.id=C.useridINNER JOINDbo. onewayairpolicy_20w aso onU.id=O.pid

Learn how to read SQL Server execution Plan (iii)--list Query Chapter