Can I create an Sql Server index that includes a summary of some child rows?

Question

Is it possible in Sql Server 2016 to create some indexed View (or equiv) that summarizes a simple Parent / Child relationship.

e.g.:

Parent: Vehicles
Children: Parts  (the parts that make up the car)
Children: Workers (the people who helped build the car)

And results like this.. e.g.:

Car   | Parts  | Workers
Car_A | 1111   | 4
Car_B | 123412 | 54
Car_C | 0      | 0

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):

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

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.

The example above is also contrite (for this question). In reality i have a larger table structure, etc. etc.

I do understand that Indexed Views can't have specific keywords, like COUNT (as mentioned by @brentozar in a blog post)

Am I looking at this problem the wrong way? I don't really want to reply on SQL caching and be a bit more proactive, here.

(Note: Maybe I've been doing many Map/Reduce queries in another NoSql db :) )

Answer 1

Sure you can make it. The data is persisted to disk so the querying is faster, but the downside is that the index (or the view) has to be maintained on every change in the underlying tables/views data.

More on it, read this.

EDIT: You've just got to use COUNT_BIG() instead of COUNT() to get the parts and workers numbers.

EDIT2: After the author's comment below I see I've made a mistake because I haven't understood it's all about the joins. I can't see a great solution to this considering you're aggregating over the vehicles...

The only solution I can see is if you're willing to have a certain "lag" in the data - then you could dump the data into a table. You could have a job that calculates the select in a temporary table/table variable and then within a transaction truncates a table and dumps the new aggregated data into it. This way your data would be accessible fast from a table and the exchange of old vs. new data would be fast, but as I mentioned it wouldn't be completely real-time data.