How to formulate a SQL Server indexed view that aggregates distinct values?
- by Jeremy Lew
I have a schema that includes tables like the following (pseudo schema):
TABLE ItemCollection {
ItemCollectionId
...etc...
}
TABLE Item {
ItemId,
ItemCollectionId,
ContributorId
}
I need to aggregate the number of distinct contributors per ItemCollectionId. This is possible with a query like:
SELECT ItemCollectionId, COUNT(DISTINCT ContributorId) FROM Item
GROUP BY ItemCollectionId
I further want to pre-calculate this aggregation using an indexed (materialized) view. The DISTINCT prevents an index being placed on this view. Is there any way to reformulate this which will not violate SQL Server's indexed view constraints?
