Why can't indexed views have a MAX() aggregate?
Sql ServerViewIndexingAggregateSql Server Problem Overview
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...
Sql Server Solutions
Solution 1 - Sql Server
These aggregates are not allowed because they cannot be recomputed solely based on the changed values.
Some aggregates, like COUNT_BIG() or SUM(), can be recomputed just by looking at the data that changed. These are allowed within an indexed view because, if an underlying value changes, the impact of that change can be directly calculated.
Other aggregates, like MIN() and MAX(), cannot be recomputed just by looking at the data that is being changed. If you delete the value that is currently the max or min, then the new max or min has to be searched for and found in the entire table.
The same principle applies to other aggregates, like AVG() or the standard variation aggregates. SQL cannot recompute them just from the values changed, but needs to re-scan the entire table to get the new value.
Solution 2 - Sql Server
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).
Solution 3 - Sql Server
Besides the reasons specified by Remus, there is less practical need to support MIN and MAX. Unlike COUNT() or SUM(), MAX and MIN are fast to calculate - you are all set after just one lookup; you don't need to read a lot of data.
Solution 4 - Sql Server
if you just want to see things ordered without adding a sort by when you use a view I just add a column with and order by in it.
id = row_number() over (order by col1, col2)