Views or Stored Procedure or UDFs : WHEN to use them ??
-
VIEWS : The purpose of a view is to select a data set from one or more tables, they do not have parameters
STORED PROCEDURE : The purpose of a stored procedure is to build intermediate results that are to be loaded into temporary table (or tempdb or otherwise), which is then queried in a select statement.
USER DEFINED FUNCTIONS
So when to use User Defined Functions
Instead of stored procedures used as parameterized views.
Instead of stored procedures that return a scalar result.
Instead of a temp table in a from clause.
Stored procedure candidates for conversion => The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. You can handle this scenario using an inline table-valued function. The stored procedure does not perform update operations (except to table variables). The stored procedure returns one result set.
QUESTION : Do you agree with this order of use and understanding of the use of such objects ?
-
VIEWS : The purpose of a view is to select a data set from one or more tables, they do not have parameters
Views can have a few purposes, they can be used to limit the visible colums in a single table as a security mechanism. They can also provide a consolidated view of a particular resultset to simplify another query. They can also be used to aggregate data, this is mostly done with indexed views in datawarehousing environments.
STORED PROCEDURE : The purpose of a stored procedure is to build intermediate results that are to be loaded into temporary table (or tempdb or otherwise), which is then queried in a select statement.
Stored Procedure do not necessarily have to load data into a temporary table, and quite often this is to be avoided if possible. Quite often the stored procedure is just the select statement itself. If your resultset is beyond the bounds of a single T-SQL statement you could probably use views to simplify.
USER DEFINED FUNCTIONS
So when to use User Defined Functions
Instead of stored procedures used as parameterized views.
Instead of stored procedures that return a scalar result.
Instead of a temp table in a from clause.
The best use for a scalar user defined function is when it is only called once. If you use a scalar function as part of a select query it will execute once for each record on the resultset. This will lead to unnecessarily long processing times.
The use of a table function should be determined by how many rows will be returned. You can't index or otherwise optimise the performance of table functions, so if you're dealing with a lot of records it may be best to use a temp table which you can index appropriately.
Stored procedure candidates for conversion => The logic is expressible in a single SELECT statement but is a stored procedure, rather than a view, only because of the need for parameters. You can handle this scenario using an inline table-valued function. The stored procedure does not perform update operations (except to table variables). The stored procedure returns one result set.
QUESTION : Do you agree with this order of use and understanding of the use of such objects ?
Not sure of your point here. Are you saying that a SQL statement is a candidate for conversion to a stored procedure or vice versa?
--------------------
Colt 45 - the original point and click interface
-
This post was just trying to find out the best way to use each of the three objects in different situations, if you have good advice on how to use them in relation to each other then please advise.Thanks
-
Generally you would put everything in stored procedures. Even simple selects that return all records from a table (eg: list of companies for a combo box). The main overall benefit of doing this is that you assign execute permissions to the stored procedure without assigning permissions to the table. So if Joe users decides to link the table into his Access database, or Excel spreadsheet, he'll won't have access to the data.
Rather than repeat everything here, take a look at this article, http://www.sql-server-performance.com/vk_sql_best_practices.asp
Vyas' website (http://vyaskn.tripod.com/index.htm) also has other articles like security best practices.
Also, Microsoft has begun nailing down some of its Patterns and Practices to provide guidance for developers, http://www.microsoft.com/resources/practices/default.mspx
--------------------
Colt 45 - the original point and click interface -
I'd give a nod to what PhillCart said. However I'd add that for me it is largely a question of performance.
SPs give the best performance of the three objects of which you speak. This is because not only is the result set stored in buffer cache for quick reuse, but it also stores the plan to execute the SP in cache also, thereby eleminating the need for the optimizer to create another plan.
Views fill a gap that SPs leave open. That I am aware, there is only ONE occasion for SPs to be used as a table to which one can join or use in a sub-query. However, views are most excellent at this. I can easily join tables to views, and views to views - not so with SPs. Views have the downside of not being able to accept parameters. To that end, MS created two new functions in SQL 2000, Muti-statement Table Valued Functions, and the other one whose name escapes me at the moment (in line table valued? - something like that). It is my understanding that views do not offer quite the performance that an SP does due to limitations for caching plans.
I use functions the least, but boy can they be useful. The standard scaler functions do not have the performance of SPs, but then they are only meant to return a single value. I have used them on occasion to enhance the format of a column's output, For example, I am not aware of a function that will format a decimal value into money with dollar signs and commas, etc. Therefore I wrote my own. Its use looks like this:
Select dbo.formatmoney(amount)
,convert(varchar(15),date_purchased,107)
from purchases
Thus, the dbo.formatmoney() function looks at the decimal value and adds the leading dollar sign, and inserts commas where necessary.Hey, if anyone knows of system function that does this, I'll raise my root beer mug to thee in toast.
I would be remiss if I didn't mention that performance can be obtained with functions by using table valued functions in conjunction with indexed views. Mind you, indexed views are picky little devils that require just the right settings on the SQL server.
I hope that helps.
-
Hey, if anyone knows of system function that does this You should be able to achieve the same using CONVERT
e.g. SELECT '$'+CONVERT(varchar,CAST(123456.78 as money),1)
But then you probably knew that anyway
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply