title: Does MS SQL Server consolidate repeated aggregates within a single query?
body: <p>I'm working on performance tuning some aggregate-heavy queries, and I'm wondering whether repeating the same aggregate function over and over has a significant performance penalty.</p>
  
  <p>I'm assuming MS SQL Server is intelligent enough to calculate each repeated aggregate only once, and then reuse the resultant value each time that exact aggregate is encountered within the same query - Is my assumption correct here?</p>
  
  <p>The alternative to this is, we can add a bunch more joins to this view (joining the same tables over and over again), with varying <code>join on</code> clauses, to group rows together in a bunch of different ways to produce the various totals without reusing any aggregate functions more than once - but looking at the execution plans we can see that adding more joins definitely <em>does</em> make the query take longer, and we technically already have all the information we need with the current number of joins anyway (we just have to perform the addition to produce the compound totals).</p>
  
  <p>Here's some example code, from one of the views in question:</p>
  
  <pre><code>COUNT_BIG ( [UVCE]. [ID] ) AS [TotalU] ,
  COUNT_BIG ( [SVCE]. [ID] ) AS [TotalS] ,
  COUNT_BIG ( [TVCE]. [ID] ) AS [TotalT] ,
  COUNT_BIG ( [CVCE]. [ID] ) AS [TotalC] ,
  COUNT_BIG ( [WVCE]. [ID] ) AS [TotalW] ,
  /* More individual totals, etc. */
  
  COUNT_BIG ( [SCE].  [ID] ) +
  COUNT_BIG ( [TCE].  [ID] ) +
  COUNT_BIG ( [CCE].  [ID] ) +
  COUNT_BIG ( [WCE].  [ID] ) +
  COUNT_BIG ( [UVCE]. [ID] ) +
  COUNT_BIG ( [SVCE]. [ID] ) +
  COUNT_BIG ( [TVCE]. [ID] ) +
  COUNT_BIG ( [CVCE]. [ID] ) +
  COUNT_BIG ( [WVCE]. [ID] ) AS [OverallTotal] ,
  
  CASE WHEN COUNT_BIG ( [SCE].  [ID] ) +
            COUNT_BIG ( [TCE].  [ID] ) +
            COUNT_BIG ( [CCE].  [ID] ) +
            COUNT_BIG ( [WCE].  [ID] ) +
            COUNT_BIG ( [UVCE]. [ID] ) +
            COUNT_BIG ( [SVCE]. [ID] ) +
            COUNT_BIG ( [TVCE]. [ID] ) +
            COUNT_BIG ( [CVCE]. [ID] ) +
            COUNT_BIG ( [WVCE]. [ID] ) &gt;= 64 THEN 4E0 ELSE (
            COUNT_BIG ( [SCE].  [ID] ) +
            COUNT_BIG ( [TCE].  [ID] ) +
            COUNT_BIG ( [CCE].  [ID] ) +
            COUNT_BIG ( [WCE].  [ID] ) +
            COUNT_BIG ( [UVCE]. [ID] ) +
            COUNT_BIG ( [SVCE]. [ID] ) +
            COUNT_BIG ( [TVCE]. [ID] ) +
            COUNT_BIG ( [CVCE]. [ID] ) +
            COUNT_BIG ( [WVCE]. [ID] ) )
            / ( 64 / 4E0 ) END AS [Score]
  </code></pre>