Why can't indexed views have a MAX() aggregate?

Question

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...

Rocca

Answer 1

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.

Succinic

They could be supported if you constrained the table to allow only insert operations, not update or delete. (If you wanted to do one of those you'd have to drop the indexed view and recreate it afterwards.) Quite a lot of tables are insert-only in practical use, and would benefit from a way to speed up max and min queries. – Lemoine

In fact the changed row could only compare with the cached data result, chossing the highest for MAX and lowest for MIN. Why they don't do this? – Condom

@Iúri dos Anjos Because then if you updated a row that used to be the MAX and made it less then it would still have to scan the whole rest of the table to look for lower values. So you're wondering 'WHY DONT I GET TO DECIDE!!' I guess it comes down to guaranteed performance, with the allowed operations only operating on that single row and if you allowed a MAX to be run it could be scanning a million rows everytime an insert was made. But it is definitely a pain if you expect the MAX value to only increment (like for my data) – Lenrow

I am a little confused about AVG. Since Count_BIG and SUM are supported, why not AVG? No re-scan is needed. – Beefwood

@Beefwood good question and I'm not sure the answer. I think is something about NULL handling... – Succinic

You can make your own AVG by storing the sum and total SUM(CatsQty) AS TotalCats, COUNT_BIG(*) AS TotalCount separately and calculating it instantly in your SELECT with SELECT TotalCats / TotalCount AS AverageCatsQty FROM AnimalsView. Of course that's only the MEAN average. – Lenrow

I still think it is a good idea to add max and min but limit the table to insert only this can be very useful for transaction tables. right now I think we can achieve similar thing with triggers but I have not done it yet and I am not sure about performance – Forney

This is a great explanation – James

Answer 2

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).

Stress

This post does not answer the OP’s question of why MIN/MAX isn’t allowed. – Ingulf

Answer 3

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) 

Wobbly

SQL Server's Indexed Views cannot use window functions like ROW_NUMBER(). – Ingulf

Answer 4

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.

Frizzle

probably someone that had a solid reason for needing MIN or MAX. I found this question while looking for a way to create just such an index, as it would take a query from minutes to seconds. Maintaining it manually seems to be the only solution, but it's not one I particularly like... – Ethiopian

I'll elaborate :-) You have it backwards. The point is that INITIALLY when creating the index all of COUNT, SUM, MAX and MIN are simple and all take the same time. But when you add, delete or update a row all need to be recalculated. So for COUNT if you deleted then you just subtract one, for SUM if you add a row you just need to ADD the corresponding row's value to the 'running total'. However for MAX and MIN you will ALWAYS need to do a scan if the newly inserted or changed value is INSIDE the current range for MIN to MAX. That could be millions of records, which could block. etc etc. – Lenrow

Also I'm not sure what you mean by 'less practical need'. Whether they're common or needed or not will entirely depend upon what your data represents. – Lenrow

@Simon_Weaver... that is only partially true. If you are adding a row then you can compare the current value to the stored min/max as the previous records are deterministic with the current stored value. If you are removing a row you only need to recalculate all if your current value is equal to the current min or max value. the add case has no effective difference to the count/sum and the delete row has a fairly limited chance of having an impact. – Collectivism