MS SQL

MS SQL RSS

Computed column in view VS trigger on update

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Sep 2006

Posts: 1

Reputation: waolly is an unknown quantity at this point

Solved Threads: 0

waolly

Offline

Newbie Poster

Computed column in view VS trigger on update

Sep 7th, 2006

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!

•

Join Date: Feb 2005

Posts: 1,181

Reputation: hollystyles will become famous soon enough

Solved Threads: 67

hollystyles hollystyles is offline

Offline

Veteran Poster

Re: Computed column in view VS trigger on update

Sep 7th, 2006

Use SQL Servers AVG keyword/function

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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)
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)