Updating SQL Server Database

Question

I have a database AfricanRatings on which I used the query

INSERT INTO AfricanRatingsAVG
SELECT RecipeID,
       Count(*) AS cnt,
       AVG(Rating)
FROM   AfricanRatings
GROUP  BY RecipeID
ORDER  BY RecipeID 

to get the average rating per recipe and the total count per recipeID. The average and the count were placed into a table AfricanRatingsAVG via the query. On the initial query, AfricanRatingsAVG is updated but not with subsequent ratings. I have tried all sorts of UPDATES to get the AfricanRatingAVG table to update every time a new rating is added to the AfricanRatings table, however, no update occurs. Any guidance in the right direction would be appreciated.

Answer 1

Use an indexed view instead:

CREATE VIEW AfricanRatingsAVG
WITH SCHEMABINDING
AS
SELECT RecipeID,
       COUNT_BIG(*) AS cnt,
       AVG(Rating) as rating
FROM   dbo.AfricanRatings
GROUP  BY RecipeID;
GO

CREATE UNIQUE CLUSTERED INDEX cdxAfricanRatingsAVG on AfricanRatingsAVG(RecipeID);

The engine will maintain the cnt and avg for you. Use the NOEXPAND hint when querying the view:

When NOEXPAND is specified for a view, the query optimizer considers using any indexes defined on the view.