gward 98556 (4/14/2015)

---

For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.