SQL Server Performance Forum – Threads Archive

views or tables

Hi I am after some advice about whether it is better to access data through a view or to go straight to the table. I have been on the microsoft programming SQL server 2000 course and they recommend to go through a view and never directly to the table so that there are no permissions on the table itself. However, someone else who has just started in our company and has been using SQL server abit in his last job has the other opinion that you should go straight to the table because using a view effects the performance as SQL server has to process the view when you request info from it. I’m not sure which is the best way to do it so any advice would be great. Thanks CE

---

Views are a useful tool to have in your database tool arsenal. More portable than functions, more flexible than stored procedures, view can give your database more secure, more performant, and easier to use http://odetocode.com/Articles/299.aspx
and also you can create optimize viewshttp://www.informit.com/articles/article.asp?p=130855&rl=1

---

You are right in your opinion. Prefer a view to access the table rather than direct accessing the table. Why does your coworker thinks direct access is faster. Maybe he can give a proof of concept. Actually where should be a difference between SELECT this FROM that and CREATE VIEW dbo.viewme AS
SELECT this FROM that ? Once they are in cache, there is no extra processing needed. They are "executed" from cache.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

When view is complex (includes many joins, group by or something like that) then you may have simpler final query when you acces tables directly. If view is just select * from table than performance should be the same.

---

I was under the impression that querying a view was slowed than running a query directly…if i have a view that joins a few tables, and then i run a select on it to return one row, doesnt SQL Server build the whole view internally and then select from that? in a query run directly on the tables, doesnt it limit the number of rows that have to be joined as early as possible requiring less rows to be joined???(and wouldnt this make it quicker??) Please tell me if this is wrong!!! ‘I reject your reality and substitute my own’ – Adam Savage

---

A bit of side information:http://www.sql-server-performance.com/tuning_joins.asp refers
If you have a query with many joins, one alternative to denormalizing a table to boost performance is to use an Indexed View to pre-join the tables. An Indexed View, which is only available from SQL Server 2000 Enterprise Edition, allows you to create a view that is actually a physical object that has its own clustered index. Whenever a base table of the Indexed View is updated, the Indexed View is also updated. As you can imagine, this can potentially reduce INSERT, UPDATE, and DELETE performance on the base tables. You will have to perform tests, comparing the pros and cons of performance in order to determine whether or not using an Indexed View to avoid joins in query is worth the extra performance cost caused by using them. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I think so.
I think it does it in both cases. Direct table access is better when you don’t need to access some of joined tables in view but using view you don’t have choice but to join them (because that join is part of view definition). Also, when you need to force an execution plan using hints and special coding techniques view may prevent you from building the most optimal query.

---

I feel it depends on the situation.
Sometimes using views is good as u can have indexed views and good in terms of security.
People will get access to those column data which you have in view.
I have not seen any performance issue between using table or view.
its better in some scenario to go with views.

---

If you access data from direct table , it will coz security threat also coz ppl will know your column definations, table name etc. while if you use view they wont know from where the data comes (Information Hiding) and let them know only what they wants.<br /><br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />][8D]<br /><br />hsGoswami<br />[email protected]

---

]]>