>>>I'm compiling some training resources for a new hire, and I need to come up with a decent list of differences between Stored Procs and UDFs in SQL Server and why you should use which.
>>>
>>>Here's what I have off the top of my head:
>>>
>>>A UDF is more easily joined into another query.
>>>A UDF cannot update data, only retrieve.
>>>
>>>I thought I had more. Help me out, folks.
>>
>>Like Mike said, scalar UDFs can really impact performance in a bad way.
>>
>>Yes, a UDF cannot update, only retrieve.
>>
>>On using a UDF to join to another query...."sort of". You can have a table-valued function and use it like a derived table. When you want to apply individual rows from a TVF to other tables, you actually have to use CROSS APPLY. Not saying you were incorrect, just qualifying the concept.
>>
>>Table valued functions also serve another great role - since views cannot receive parameters, people use views along with TVFs as a security feature (for access to rows)
>>
>>Stored procs are for insert/update/deletes, or for bringing back full result sets. You can't use a stored proc to directly join (or apply) with other tables - not without redirecting to a temp table first.
>
>Hmmm... I take a more practical approach.
>
>I use a UDF when it will save me typing.
>If I need the customer's name often, I'll make a UDF that takes the customer ID as a parameter and returns the customer's name. It saves me typing time compared to using a join and it makes my queries more readable.
>I hadn't thought about performance because I've never noticed a problem with them.
>
>I use SP's for data changing for the same reason.
>I debug them once and use them often. I like that!
>I also read once that SP's might run more quickly than queries because the server remembers the optimal execution plan.
>That made sense to me too.

Actually, since SQL 6.5 that is a myth. All queries and sprocs have an execution plan which is in the plan cache. The only performance difference is the length of the query versus the length of the call to the sproc.

As long as two ad-hoc queries are the same except for parameterization, they reuse a plan just as for a stored procedure. That way, your FoxPro app can generate two identical queries and it will run fast rather than being compiled each time.

http://www.scarydba.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/

>
>If I were getting someone started, I'd emphasize using views wherever possible.
>They are huge time savers (my time.)