view or table join

[collierd2]

Hello

Assuming the view returns the same number of records at the table

e.g. tblItem has details of 1000 Items
VwItem also has these 1000 items but perhaps does some additional calculations

If I wanted to join to return (for example) the item description what should I join to?

Is it more efficient to join to tblItem or vwItem
OR is it the same?

Thanks

 

[drtree]

The performance should be the same. I would probably go with the view. I've had instances where I worked where someone would change the underlying table and go ahead and update the view with the new field references. If I had been using the actual table in my sql call, I would have gone back in and re-wrote it, but using the view helped me in that regard.
Here's some good info on the subject:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1170220,00.html#views

HTH,

Doc Tree

 

[SQLSister]

Depends on the implemetation, your design and the data. Test both possibiliites to see which is better. If you are using standard edition, you can't index views and they may be slower. Also suppose you are querying and you only need fields from two of the six tables inthe view, inthis case doing allthe unneeded joins would be less efficient than just querying what you need. On the other hand if you have complex relationships that you want consistently queried (say for financial reports) then views may be the best way to ensure that the data is correct. What can also make a view faster is that you can build cross-table mulitple column indexes. So like I said the real answer is it depends.

"NOTHING is more important in a database than integrity." ESquared

 

[collierd2]

Thanks for the advice