Query Optimization and Execution - Pro SQL Server Internals - page 455

Figure 25-6. Row-based model: Getting the first row in the output
Let's look how SQL Server executes such a query on operator-by-operator basis. The Select operator, which is the
parent operator in the execution plan, calls the GetRow() method of the To p operator. The To p operator, in turn, calls
the GetRow() method of the Nested Loop Join .
As you know, a Join needs to get data from two different sources to produce output. As a first step, it calls the
GetRow() method of the Sort operator. In order to do sorting, SQL Server needs to read all of the rows first, therefore
the Sort operation calls the GetRow() method of the Clustered Index Scan operator multiple times, accumulating
the results. The Scan operator, which is the lowest operator in the execution plan tree, returns one row from the
Customers table per call. Figure 25-6 shows just two GetRow() calls for simplicity's sake.
When all of the data from the Customers table has been read, the Sort operator performs sorting and returns
the first row back to the Join operator, which calls the GetRow() method of the Clustered Index Seek operator on the
Addresses table after that. If there is a match, the Join operator concatenates data from both inputs and passes the
resulting row back to the Top operator, which, in turn, passes it to Select .
The Select operator returns a row to the client and requests the next row by calling the GetRow() method of the
Top operator again. The process repeats until the first 10 rows are selected. It is worth mentioning that the operators
kept their state and the Sort operator preserves the sorted data and can return all subsequent rows without accessing
the Clustered Index Scan operator, as shown in Figure 25-7 .
Figure 25-7. Row-based model: Getting the subsequent row