SQL Server Performance Forum – Threads Archive
Procedure / Function / View?
Hi, I am preparing for SQL Server 2000 Design and Implementation Exam. One of the areas where I am making lot of mistakes is when it comes to selecting data from tables, I am not able to select the right opion – stored procedure, view or User defined function. My understanding for best conditions for these objects goes like this Stored Procedures should be used when the query needs to be called again and again so that performance is improved due to reuse of execution plan Functions should be used when the resultset needs can be used to join with other tables and also the data needs to be filtered on the basis of some parameter Views should be used Mainly for Security of data Concerns Help me out in classifying the use of these three objects….. Gauravstored procedures are used to improve performance but not only. Suppose you want to show a client some data. Suppose you have a lot of different clients. You will not write a view for each client, but a procedure that accepts client as a parameter and returns the appropriate results.
In this case, you would not give the client a direct access to run this procedure or he could exec it with whatever parameter he wishes. You would have to protect it from yout application and give the application the permission to run it.
If you have a very few clients, you would create differents views for different clients. Views are also used to improve performance, and I am talking about indexed views. The are very useful with agg functions. Functions, like you’ve said are very useful when you still need to work on the result set. Bambola.
What was the question you’re getting confused between these 3 objects?
These 3 objects have got their own advantages and importance in the SQL server. What you’d defined is basic functionality. When it comes to the question it depends how significant the object importance is. HTH _________
Satya SKJ
The question is when u have a query that can be written as view/sp/function, what are the factors that affect your decision? Gaurav
If it’s a query that an application calls I would use a stored procedure since the library on the client has special methods for preparing/calling a stored procedure and you avoid doing an EXEC. It might also be a bit faster, as I understand it. I would not use a function here since the T-SQL that can be used is more limited than in a stored procedure. It is also easier to incorporate business rules inside a SP. Once inside a SP, if you need repetitive tasks made I would use functions as a help. If you need to join inside and SP on some special data I would use a view or a function, with one of the differences being that a function can be parameterized. Joining on results from another SP can be quite tricky. As mentioned views can also be used to seperate data for security reasons. Performance wise SQL server caches plans for re-use both for stored procedures and dynamic sql, be it select * from myView or select * from myTable. /Argyle
]]>