Hugo Kornelis (3/4/2014)
Koen Verbeeck (3/4/2014)
Nice question, but the explanation doesn't mention why the order of casting vs order by changes. (because of adding the T alias in the second statement)
The ORDER BY operates by default on column aliases. In the official theoretical model of query evaluation, that is even the ONLY option and sorting on anything that is not in the SELECT list is impossible.
Luckily SQL Server does allow you to order by columns from the FROM clause even if they are not in the SELECT list, but the standard behaviour is preferred. That's why SQL Server will first try to match the ORDER BY arguments with the column names or aliases from the SELECT clause. WHen that fails, it will look at the remaining columns.
Adding the prefix bypasses this behaviour, because you now explicitly specify the source of the data.
In some queries only items the order by clause can only use columns from the select list: order by in queries that use select distinct, union, intersect, or except are not permitted to use columns from tables in teh from list unless those columns are in the from list. This applies only when those features occur at the outermost level, not in subqueries - but order clauses in subqueries are meaningless anyway. With the set operators the order by clause can only use the names and aliases specified in the first select (ie the colum names of the record set returned by the query). So in these cases when the standard behaviour fails the system doesn't look at the remaining columns.
Absolutely true. And all those restirctions are totally obvious when you think about it.
For the set operators, allowing access to different column simply opens way too many cans of worms. Think about "SELECT OrderID FROM dbo.Orders UNION ALL SELECT CustomerID FROM dbo.Customers ORDER BY OrderQty;" -assuming that the Customers table has no OrderQty column, how should SQL Server resolve this?
For SELECT DISTINCT, allowing ORDER BY to operate on columns not in the SELECT list would produce erratic and unpredictable behaviour. Consider this table:
Col1 | Col2
1 | 1
2 | 2
3 | 1
Now what do you expect to get returned if you allow "SELECT DISTINCT Col2 FROM TheTableAbove ORDER BY Col1;"?