>>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.
If I were getting someone started, I'd emphasize using views wherever possible.
They are huge time savers (my time.)
Anyone who does not go overboard- deserves to.
Malcolm Forbes, Sr.