Hi all
Which is quicker, better for returning a recordset.
A UDF or a SP.

THanks
Robert

There are a variety of factors here, did you try it and compare?

A table-valued function that just runs a query should run about the same as
a stored procedure that does the same.

The problem is, it's usually not that simple.





Show quoteHide quote

The standard response from this group is: "It depends"

Show quoteHide quote

And it became the standard response because it's so acutely accurate :)

Show quoteHide quote

Perzactly!

Show quoteHide quote

Which looks better:

mayonaisse or mustard?


Stu

Robert Bravery wrote:
Show quoteHide quote

Mayonaisse on Asparagus, mustard on hot dogs.

Different strokes for different folks. Depends on the purpose.

Show quoteHide quote

well one is made from egg, the other from mustard seed, how could they
compare, like which is better apples or oranges.
Seems to me the UDF's and SP's can be used for the same thing. My question
would be , in which case would one be better than the other

Thanks
Robert

Show quoteHide quote

Robert Bravery (m*@u.com) writes:
First of all there are three type functions: 1) scalar functions 2) inline
table functions 3) multi-statement functions.

I mainly think of functions that you use within stored procedure, but
let's assume that the context that we are to call a UDF or SP from a client.

In the case of a scalar function, the scenario would be that you want a
single value back. This can be achieved with a stored procedure or a
scalar UDF. The advantage with an SP here is that if you further down the
road realise that you need a second value, a stored procedure can easily
be modified to have a second OUTPUT parameter, whereas the UDF cannot.
Add to this that you are more limited in what you can do in a UDF. For
instance a UDF cannot call other stored procedures. Thus, SP appears to
be the best here, although it's only marginally better. Using UDFs instead
is no major flaw.

Inline-table functions? It's not really correct to compare these to
stored procedures. Rather they are parameterised views. Thus directly
selecting data from a inline table function is like running a SELECT
directly against a view or a table. If you have settled on using
stored procedures only, running SELECT against a inline table-function
is a violation of that principle.

The same reasoning can be applied for multi-statement table functions
of course. Add to this that in a multi-statement function the data
will always bounce over a table variable. Then again, the corresponding
stored procedure would in many cases too use a table variable or a
temp table. But again, the limited set of available operations in a UDF
makes a case for stored procedures.

In the end, I would say that stored procedures remains the backbone
of SQL Server programming, and functions are supplementary features
that can help you to write better queries. But as the main interface
for a client, stored procedures is the way to go.

I have to remember that my humour sometimes doesn't translate well into
text.  I was trying to point out the fact that a) stored procedures and
UDF's have different purposes (hence the may/mustard comparison), and
b) the question really isn't about performance, it's about appropriate
usage (the "which looks better" part).

Erland (as usual) gave a great explanation as to why the two are
different.  I don't think anything more needs to be added to that.

Stu

Robert Bravery wrote:
Show quoteHide quote

I think Erland was pointing out that you need to add Catsup for the third
UDF type to complete your analogy :)

Show quoteHide quote

BUG(?): Distinct + variables TSQL
SQL 2005 updating data in SQL 2000 DB via linked server returns error
ORDER BY DATETIME field BUG?
aggregate function
search in only non tagged words