Tom Brown (5/7/2010)
I attach my test script, where I was attempting to confirm the validity of my 10-year-old slip of paper, by looking at actual execution plans, and matching up the parts of T-SQL to the plan.I avoided using 'logical' because the execution plan seemed to back up the sequence of evaluation. The plans show data being moved from right to left, joined, filtered, sorted, etc. This is my explanation for the use of internal sequence of evaluation.
Perhaps these plans only represent the logical way a query is handled, and don't really reflect the true sequence of processing? If so then can we ever truely know whats going on.
Hi Tom,
The execution plan represents the actual processing order, you looked at the right thing. But you made two mistakes. One is not looking good enough, the other one is assuming that, if it holds for this example, it always holds.
Run the example again. Check the execution plans. This time, hover your mouse over any of the two table scan operators in the first query, or any of the two clustered index scan operators in the second query. Check what is listed as "Predicate". You will see that the WHERE clause has been pushed up; the Query Optimizer has decided to filter rows based on the WHERE clause because that reduces the query cost without affecting the results.
Of course, there might well be situations where the actual internally used order matches the logical processing order exactly. If there are no cheaper alternatives, that is what SQL Server will do. My point is that this will not always happen (and, in fact, almost never happens).