I have been trying out a few index views and am impressed but I nearly always need a max or a min as well and can not understand why it doesn't work with these, can anyone explain why?
I KNOW they are not allowed, I just can't understand why!!! Count etc. is allowed why not MIN/MAX, I'm looking for explanation...
Aggregate functions like MIN/MAX aren't supported in indexed views. You have to do the MIN/MAX in the query surrounding the view.
There's a full definition on what is and isn't allowed within an indexed view here (SQL 2005).
Quote:
The AVG, MAX, MIN, STDEV, STDEVP, VAR, or VARP aggregate functions. If AVG(expression) is specified in queries referencing the indexed view, the optimizer can frequently calculate the needed result if the view select list contains SUM(expression) and COUNT_BIG(expression). For example, an indexed view SELECT list cannot contain the expression AVG(column1). If the view SELECT list contains the expressions SUM(column1) and COUNT_BIG(column1), SQL Server can calculate the average for a query that references the view and specifies AVG(column1).
Because the MIN/MAX aggregate value cannot be recomputed solely based on the changed values. Aggregates like COUNT or SUM can be recomputed just by looking at the data that changed, but MIN and MAX cannot. If you ar deleting the value that is currently the MAX (or MIN), then the new MAX/MIN has to be searched and found in the entire table. Same goes for the other statistical aggregates like standard variation, they cannot be recomputed just from the values changed but need to res-scan the entire table to get the new value.