If a SELECT clause transforms a column, and then aliases the result as the same name of
the original column in the base table, the ORDER BY clause is affected and
cannot make use of the sorted data in the base table. The SELECT clause alias affects
the ORDER BY clause.
For an
example, I am using a simple two-column table with an auto-increment Clustered PK
and string column.
CASTing
the PK as a VARCHAR, aliasing it as a different name (n1) with ORDER BY the base
table PK column performs an Index Seek and leverages the base table sort.
But, CASTing
the PK as a VARCHAR, aliasing it as the base table column name again with ORDER
BY the base table PK column performs an Index Seek but must sort the data –
because the data actually is sorted as a VARCHAR
The column 'n' has its meaning changed between execution of the WHERE clause and execution of the ORDER BY clause. The transformed data is what is being ORDER'd.
To return transformed
data - named the same as the base table column and ordered as it is in the base
table PK (which was a weird requirement I recently had), you need to fully qualify the ORDER BY column reference.
