June 11, 2003 at 6:30 am
What is/are the advantages of referencing a view for data instead of just writing the query and displaying the results?
All of my code is currently displayed in HTML pages using Cold fusion.
June 11, 2003 at 8:52 am
Hello gleazenby,
quote:
What is/are the advantages of referencing a view for data instead of just writing the query and displaying the results?All of my code is currently displayed in HTML pages using Cold fusion.
first, there are security reasons. With views you have better access control on your data, because you only need to grant permission on the view, not on the underlying table(s).
second, there might be performance advantages, for views is a query plan generated when the query is saved. For queries the query plan is generated at runtime
For more information see BOL "views-SQL Server, queries and".
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 11, 2003 at 9:12 am
Not to mention the fact that the view is reusable. The query has to be rewritten many times.
Also - for use by third party products (Crystal Reports, etc.) the view can provide functionality the product cannot service through it's own interface. Crystal Reports has a huge problem with conditional joins.
my $0.02
Guarddata-
June 11, 2003 at 11:25 am
Basically, stored procedures and views are more maintainable, produce less network traffic (you don't have to send a giant SQL string across the wire). The compilation plans for both stored procedures AND ad-hoc SQL queries are stored in memory on the server, so compilation advantage is minimal, at least for the subsequent calls to the query plan. Main advantage is, like Frank said, permissions issues, and maintenance issues.
June 11, 2003 at 5:30 pm
Hi,
Rather then Views, you probabally should use Stored Procedures. ColdFusion can utilise these quite well.
eg
<CFSTOREDPROC PROCEDURE="dbo.NEWS_ITEM_EDIT" DATASOURCE="NET_NEWS">
<CFPROCPARAM TYPE="IN" DBVARNAME="@NEWSS_REFNO" VALUE="#URL.NEWSS_REFNO#" CFSQLTYPE="CF_SQL_NUMERIC">
<CFPROCRESULT NAME="NEWS_ITEM">
</CFSTOREDPROC>
June 12, 2003 at 1:07 am
views are mainly there for security reasons. I prefer to use views for frequently (to be) used joins, so join-predicates are predefined. Stored procedures on the other hand can provide more functionality and should be prefered for db-access.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 12, 2003 at 2:04 am
We use ColdFusion and a ColdFusion based content management system by PaperThin called CommonSpot.
I encourage my developers to avoid direct access to tables and to use stored procedures for just about everything. We gained a huge performance boost by using this method.
If I understand the process, to run a direct query on a ColdFusion based site.
A stored procedure has already worked out how best to run your query and therefore SQL Server can return the records much faster.
Direct selects may also open up your site for SQL Injection attacks.
June 12, 2003 at 4:36 am
Using views and procedures will decrease the administrative burden of upgrading/maintaining your code, since only the back end objects have to be changed. Compare that with having to change every ad hoc query hitting the server, and you will see the benefit.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply