SQL Server, Indexed View for two similar queries

sql2k Standard sp3

How do Indexed Views work when two different queries are able to take
advantage of them but use different aggregations?

create table t1(c1 int identity (1,1), c2 char(10))
create table t2(c1 , c3 int)

Now I want to have a query that:

select t1.c1,c2,sum(t2.c3)
from t1
inner join t2 on t1.c1 = t2.c1
group by t1.c1,c2

From the little I know about Indexed Views, this would be a great candidate.

But then I also have another query that:

select t1.c1,avg(t2.c3)
from t1
inner join t2 on t1.c1 = t2.c1
group by t1.c1

Do I need a whole other Indexed View for this since query1 I used Sum and
query2 I used Avg? That could get costly quick and Im sure Im not the first
one to run into this.

TIA, ChrisR

When you create an aggregate indexed view, you have to add a
COUNT_BIG(*). In combination with a SUM aggregate, this can easily be
used to calculate the average (AVG = SUM(..)/COUNT).

So all you need to do is rewrite your avg(t2.c3)

Hope this helps,
Gert-Jan



--
(Please reply only to the newsgroup)