Max function cannot be used in an indexed Views

All,
Does anyone know a workaround for the restriction to use max in an
indexed view? seems it really can help us to improve performance, but
we MUST use max to define the view...
any suggestions?
10x
GCThere isn't. This is because indexed views are built in such a way, that
each individual transaction can be processed without having to rebuild
the entire indexed view. This is not possible if there is an aggregate
such as AVG, MIN, MAX.
You could build your own 'materialized view' and maintain it using
triggers on the base table. However, deletes or updates of the maximum
value of a group might have serious performance impact. Proper indexes
could mitigate this problem.
HTH,
Gert-Jan
GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC|||GC wrote:
> All,
> Does anyone know a workaround for the restriction to use max in an
> indexed view? seems it really can help us to improve performance, but
> we MUST use max to define the view...
> any suggestions?
> 10x
> GC
No way, and usully no need to do it either. My understanding of why is
as follows:
First reason, usually MAX and MIN can be selected from a proper index
with a quick index seek - there usually is no need to further speed it
up. Second reason, it would be not that simple to re-calculte MAX and
or MIN if you delete rows with that (MAX or MIN) value. So,
implementing that would be a lot of pain in the neck for little
practical advantage.
I think just using a proper index should be good enough under most
circustances.
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||On 2 Jan 2007 05:01:25 -0800, GC wrote:

>All,
>Does anyone know a workaround for the restriction to use max in an
>indexed view? seems it really can help us to improve performance, but
>we MUST use max to define the view...
>any suggestions?
>10x
>GC
Hi GC,
I'm afraid you'll have to rol your own. You can duplicate the "under the
cover" implementation of an indexed view by creating a table to store
the results, and populating it by using INSERT INTO ... SELECT and then
the query you currently have in your view. Next, to keep it current, add
triggers on the base tables.
Simple example (untested) - note that the second update statement in the
trigger, the one that finds the new max if the old max gets deleted or
updated, can make the trigger very slow!!
CREATE TABLE dbo.TestTab
(a int NOT NULL PRIMARY KEY,
b int NOT NULL)
go
CREATE VIEW dbo.TestView WITH SCHEMABINDING
AS
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE UNIQUE CLUSTERED INDEX TestIndex ON dbo.TestView(b);
go
DROP VIEW dbo.TestView;
go
CREATE TABLE dbo.TestView
(b int NOT NULL PRIMARY KEY,
MaxOfA int NOT NULL);
INSERT INTO dbo.TestView (b, MaxOfA)
SELECT b, MAX(a) AS MaxOfA
FROM dbo.TestTab
GROUP BY b;
go
CREATE TRIGGER TrgTestView1
ON TestTab FOR INSERT, UPDATE, DELETE
AS
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM inserted AS i
WHERE i.b = TestView.b)
WHERE EXISTS (SELECT *
FROM inserted AS i
WHERE i.b = TestView.b
AND i.a > TestView.MaxOfA);
UPDATE TestView
SET MaxOfA = (SELECT MAX(a)
FROM TestTab AS t
WHERE t.b = TestView.b)
WHERE EXISTS (SELECT *
FROM deleted AS d
WHERE d.b = TestView.b
AND d.a = TestView.MaxOfA);
go
DROP TABLE TestView;
DROP TABLE TestTab;
go
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 03 Jan 2007 00:54:05 +0100, Hugo Kornelis wrote:
(snip)
>Simple example (untested) - note that the second update statement in the
>trigger, the one that finds the new max if the old max gets deleted or
>updated, can make the trigger very slow!!
I just realized I forgot to put in code to add a row to TestView if a
new value for b is added, and code to remove a row if the last occurence
of a value is removed. Something like this:
INSERT INTO TestView (b, MaxOfA)
SELECT i.b, MAX(i.a)
FROM inserted AS i
WHERE NOT EXISTS
(SELECT *
FROM TestView AS t
WHERE t.b = i.b);
and
DELETE FROM TestView
WHERE EXISTS
(SELECT *
FROM deleted AS d
WHERE d.b = TestView.b)
AND NOT EXISTS
(SELECT *
FROM TestTab AS t
WHERE t.b = TestView.b);
The latter will, again, be slow.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis