1

I am trying to create an indexed view with using aggregate AVG, but can't make it work. Has anybody tried doing it? I found this article but couldn't figure out how to make it work. Here is my script:

CREATE VIEW MyView 
WITH SCHEMABINDING 
AS 
    SELECT 
        Id,  AVG(Age) as AvgAge, COUNT_BIG(*) as CountBig 
    FROM 
       dbo.Student 
    GROUP BY 
       Id;
GO

CREATE UNIQUE CLUSTERED INDEX MyIndex ON MyView(Id);

I understand MIN and MAX are not possible, but why not AVG?

Dilshod
  • 3,189
  • 3
  • 36
  • 67
  • [This previous answer](http://stackoverflow.com/a/2134890/1225845) should explain it - basically, because AVG (like MIN and MAX) needs to rescan all available members to recompute if a record was added, deleted, or changed. – AHiggins Aug 24 '15 at 15:54
  • 1
    I am not sure why my question is marked as Duplicate. Other question doesn't say anything about AVG. – Dilshod Aug 24 '15 at 15:59
  • "Duplicate" has a slightly broad meaning here: the two questions have the same answer, even though the other question specifically references MAX and MIN, and your question is about AVG. See [here](http://meta.stackexchange.com/a/10844/302608) for a little more explanation. – AHiggins Aug 24 '15 at 16:02

0 Answers0