How to formulate a SQL Server indexed view that aggregates distinct values?

Question

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?

Answer 1

SELECT
   ItemCollectionId,
   COUNT(DISTINCT ContributorId),
   COUNT_BIG(*) AS DummyColumn
FROM Item
GROUP BY ItemCollectionId

An aggregate will require COUNT_BIG(*) as mentioned in MSDN.

This also says "no DISTINCT" and I'm not sure (never tried it, sorry) if this applies to it's use in an aggregate (as you have), or SELECT DISTINCT...

Answer 2

Not possible, apparently.