Wednesday, March 28, 2012

MAX in indexed views

Hi,
Is there workaround to use MAX aggregate funcion in indexed views?
I use SS2000, SP4, Win 2000 Advance, SP4.
Thanks in advance
Nikola MilicNo there is not, and the reason is simple: when you modify a table, the
database engine is supposed to modify the indexed view based on only
the modified row and the relevant row in the view, not accessing
anything else. This is necessarty to minimize lock contention. What
happens if you delete the only one row with the MAX value? You will
have to access at least an index to get the smaller MAX value. That may
introduce lock contention and deadlocks, and it will complicate the
implementation, so it's not supported.
With SUM, it's a different story: when you delete a row, you decrement
the corresponding BIG_COUNT, and either decrement the SUM, or delete
the row from the indexed view altogether - no need to access anything
else...

No comments:

Post a Comment