SELECT Clause Aliases Affect ORDER BY clause

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.