I am wondering whether SQL Server knows to 'cache' if you like aggregates while in a query, if they are used again.
Select Sum(Field), Sum(Field) / 12 From Table
Would SQL Server know that it has already calculated the Sum function on the first field and then just divide it by 12 for the second? Or would it run the Sum function again then divide it by 12?
It calculates once
Select Sum(Price), Sum(Price) / 12 From MyTable
The plan gives:
|--Compute Scalar(DEFINE:([Expr1004]=[Expr1003]/(12.))) |--Compute Scalar(DEFINE:([Expr1003]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END)) |--Stream Aggregate(DEFINE:([Expr1010]=Count(*), [Expr1011]=SUM([myDB].[dbo].[MyTable].[Price]))) |--Index Scan(OBJECT:([myDB].[dbo].[MyTable].[IX_SomeThing]))
This table has 1.35 million rows
- Expr1011 = SUM
- Expr1003 = some internal thing to do with "no rows" etc but is Expr1011 basically
- Expr1004 = Expr1011 / 12