in my sql statement I call sum twice for the same argument, is it duplicating the effort?
SQL Server reuses the aggregates.
In fact, if you build the query plan, you will see the
SUM in a result set of an aggregation operator (like
Stream Aggregate) denoted as something like
The value of this expression will later be used as an input to the other operators.
Here's the sample query:
SELECT ROUND(SUM(id), -1)FROM masterGROUP BY nameORDER BY SUM(id) DESC
and it's plan:
|--Compute Scalar(DEFINE:([Expr1004]=round([Expr1003],(-1)))) |--Sort(ORDER BY:([Expr1003] DESC)) |--Stream Aggregate(GROUP BY:([test].[dbo].[master].[name]) DEFINE:([Expr1003]=SUM([test].[dbo].[master].[id]))) |--Index Scan(OBJECT:([test].[dbo].[master].[ix_name_desc]), ORDERED BACKWARD)
As you can see, the aggregation is done once and stored in
Expr1003 is then reused in both the
Sort operator (which processes the
ORDER BY) and
Compute Scalar (which processes