title: Can I create an Sql Server index that includes a summary of some child rows?
body: <p>Is it possible in Sql Server 2016 to create some indexed View (or equiv) that summarizes a simple Parent / Child relationship.</p>
  
  <p>e.g.:</p>
  
  <pre><code>Parent: Vehicles
  Children: Parts  (the parts that make up the car)
  Children: Workers (the people who helped build the car)
  </code></pre>
  
  <p>And results like this..
  e.g.:</p>
  
  <pre><code>Car   | Parts  | Workers
  Car_A | 1111   | 4
  Car_B | 123412 | 54
  Car_C | 0      | 0
  </code></pre>
  
  <p>I guess if I was to write this as a SQL query (which I'm assuming is not indexed then and has to 'calculate' the entire answer):</p>
  
  <pre><code>SELECT          a.CarId
  FROM            Cars a
  LEFT OUTER JOIN Parts b ON a.CarId = b.CarId
  LEFT OUTER JOIN Workers c ON a.CarId = b.CarId
  </code></pre>
  
  <p>Now the reason I'm after an 'INDEXED' view is that I'm assuming that the calculations are stored on disk so I only have to do a scan of the index to get ther results, instead of having to calculate the results, on exection.</p>
  
  <p>The example above is also contrite (for this question). In reality i have a larger table structure, etc. etc.</p>
  
  <p>I do understand that Indexed Views can't have specific keywords, like <code>COUNT</code> (as mentioned by <a href="https://www.brentozar.com/archive/2013/11/what-you-can-and-cant-do-with-indexed-views/" rel="nofollow noreferrer">@brentozar in a blog post</a>)</p>
  
  <p>Am I looking at this problem the wrong way? I don't <em>really</em> want to reply on SQL caching and be a bit more proactive, here.</p>
  
  <p>(Note: Maybe I've been doing many Map/Reduce queries in another NoSql db :)  )</p>