SUM same column twice performance
was wondering does Adding the same column twice cause an impact on Performance? Meaning does SQL Server add the column twice? Or will it add the column once and use for both columns in the result set?


Sum will be calculated once and result is used for multiple columns referring the same sum

Below is sample setup from my instance ,which will prove the same

select 
sum(empid) as empidsum,
sum(empid)*2 as empidsum,
sum(empid)*4 as empid4
 from orders


execution plan:


In the above execution plan,highlighted computed scalar shows below  

Scalar Operator([Expr1003]*(2))  
Scalar Operator([Expr1003]*(4))  


Where Expr1003 is the sum calculated by previous compute scalar


  should I use a CTE with one SUM like the following


CTE is just for readability,using a CTE won't help performance .

There are cases where materialzing CTE will help in improving the query.Look out this answer for more details:

Which are more performant, CTE or temporary tables?