Computed column in view VS trigger on update

[waolly]

Hi guys,

Let me describe the situation I am in. I have a table of 2000 objects and each object has a range of different prices associated with it, stored in another table.

I have created a view which serves to list the objects as well as their average price. At present I have something along the lines of

CREATE VIEW objview AS
select o.*, (select sum(op.price)/count(*) from objprice op where op.objectid=o.objectid) averageprice
from obj o

I have a feeling that the above might not be very efficient.

Does anyone have any input regarding speed and efficiency on
- using a join in the above instead of a nested query
- having a fixed field in the original obj table which gets updated via a trigger when new values are inserted into objprice

Which is the best solution?

Thanks!

[hollystyles]

Use SQL Servers AVG keyword/function

SELECT
    o.column1, o.column2, ...
    avg(p.price)
FROM
    Objecttable o
    JOIN Pricetable p on o.id = p.id
WHERE
    <put criteria if needed otherwise leave WHERE out>
GROUP BY 
    o.column1, 0.column2, .... (but not the column in the avg() expr)