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

sql-server view indexing aggregate
alex picture alex · Jan 25, 2010 · Viewed 14.6k times · Source

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

Answer

Remus Rusanu picture Remus Rusanu · Jan 25, 2010

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.

Related questions

Cannot create index on view 'View_Table_Name' because the view is not schema bound

I am using Views in my stored Procedure(SQL-Server). For Improving Performance, I have tried to created INDEX of that View. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW VW_Table_Name AS SELECT Col1,Col2,…

Read More
Is there a way to retrieve the view definition from a SQL Server using plain ADO?

I'm successfully extracting column definitions from databases hosted on a SQL server using the ADO Connection OpenSchema() call in its various incarnations so I can programmatically recreate those tables in another SQL database. So far, so good. The main interaction …

Read More
How can I check if a View exists in a Database?

I have some SQL code that needs to be executed if a certain View exists in a database. How would I go about checking if the View exists? EDIT: The DBMS being used is Microsoft SQL Server

Read More