CELKO (1/4/2013)
h) The ORDER BY clause is part of a cursor, not a query. The result set is passed to the cursor, which can only see the names in the SELECT clause list, and the sorting is done there. The ORDER BY clause cannot have expression in it, or references to other columns because the result set has been converted into a sequential file structure and that is what is being sorted.
Not that it's a particularly useful example of a sort but, just to prove how wrong virtually everything in the statement above can actually be in T-SQL, please consider the following...
SELECT service_broker_guid
FROM sys.Databases
ORDER BY CASE
WHEN database_id < = 4
THEN N' ' + name
ELSE CAST(create_date AS NVARCHAR(128))
END
Note that nothing in the SELECT list is referenced by the ORDER BY, that the ORDER BY does, in fact have several expressions in it and is really just one big expression to begin with, and that it not only references columns outside the SELECT list but conditionally sorts based on the content of two entirely different columns.
CELKO (1/4/2013)
a) Effectively materialize the CTEs in the optional WITH clause. CTE's come into existence in the order they are declared so only backward references are alllowed.{snip}
As you can see, things happen "all at once" in SQL, not "from left to right" as they would in a sequential file/procedural language model.
As you can see in your own writing, "It Depends". Some things don't actually happen "all at once" in SQL. In fact, even an update on a single column doesn't happen all at once. It happens just like procedural code... one row at a time, one page at a time.
--Jeff Moden
Change is inevitable... Change for the better is not.