Tutoring a Junior Developer :: SQL Server 2012

1. When I try to refer to a column alias that I defined in the SELECT list in the WHERE clause, I get an error. Can you explain why this isn't allowed and what the workarounds are?
   
2. Referring to a column alias in the ORDER BY clause seems to be supported. Why is that?
   
3. Why is it that Microsoft made it mandatory to specify an ORDER BY clause when using OFFSET-FETCH but not when using TOP? Does this mean that only TOP queries can have nondeterministic ordering?

1. To be able to understand why you can't refer to an alias that was defined in the SELECT list in the WHERE clause, you need to understand logical query processing. Even though the keyed-in order of the clauses is SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY, the logical query processing order is FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY. As you can see, the WHERE clause is evaluated prior to the SELECT clause, and therefore aliases defined in the SELECT clause aren't visible to the WHERE clause.
   
   
2. Logical query processing order explains why the ORDER BY clause can refer to aliases defined in the SELECT clause. That's because the ORDER BY clause is logically evaluated after the SELECT clause.
   
   
3. The ORDER BY clause is mandatory when using OFFSET-FETCH because this clause is standard, and standard SQL decided to make it mandatory. Microsoft simply followed the standard. As for TOP, this feature is proprietary, and when Microsoft designed it, they chose to allow using TOP in a completely nondeterministic manner -- without an ORDER BY clause. Note that the fact that OFFSET-FETCH requires an ORDER BY clause doesn't mean that you must use deterministic ordering. For example, if your ORDER BY list isn't unique, the ordering isn't deterministic. And if you want the ordering to be completely nondeterministic, you can specify ORDER BY (SELECT NULL) and then it's equivalent to not specifying an ORDER BY clause at all.