Expand aggregate support in indexed views (MIN/MAX)
4/3/2007 12:00:38 PM
I often think that my reporting queries would benefit from indexed views
where the system would maintain for me the element along with the min or max
of some column. For example, a lot of the reports we run ask, "when was the
last time x happened?" Unless the clustered index is on x, eventdate desc,
this query can be a very expensive scan over 30MM or 300MM rows.
I understand now that the overhead here to maintain the index is much more expensive than simply incrementing / decrementing a count. Not only do we have to compare new / changed values to the existing min / max in the view, we also have to find the "next" highest or lowest value when the row associated with the current value is deleted or changed. However, if given the ability, I would like the option to create such a view and compare index maintenance overhead with query performance.
If implemented, I can see that it would only make sense for certain datatypes, e.g. datetime and numeric types would be allowed, but I don't see why NVARCHAR(MAX) or XML or IMAGE would have to be. I'd also be fine with a limitation of one such aggregate per view.
And as Adam suggests, you should properly implement windowing functions, e.g. in a view with the following query:
TheRow = ROW_NUMBER() OVER (PARTITION BY Col1, ORDER BY Col2 DESC)
Let me create an index on, for example, (Col1, theRow) ... have it implemented properly and make sure the optimizer can handle all scenarios. This index was allowed in RTM but, as Stefano explained, it was an oversight that was fixed in the SP1 timeframe.