Is there a good reason for using User Defined Functions in SQL Server?

Question

Over the time, SQL Server has reduced the gap between UDFs and Stored Procedures. Many a times, you would be right using one or the other. The rule of thumb is Functions do a specific thing (getdate()), and Procedures usually do multiple things—many a times, complex business logic (sp_addarticle).

Our development team has created hundreds of functions, that are really causing performance issues. No matter how many rows a functions return, execution plan treat them as if they are returning one single row. This is causing the performance issues. Whenever there’s a bottle neck, we just convert the Functions into Procs! This approach is becoming a trend.

I know there are pros and cons for going one way or other. I have a couple of questions:

• Is there any negatives in converting all our Functions into Procs? (It’s going to be a Herculean task! But we might have to bite that bullet.)

• Is there anything that can be achieved ONLY in Functions? (I know there are things only UDFs do. For e.g. you can call a function in a SELECT, or function can be referred in DEFAULTS. I’m not looking for such things.)

• What’s the maximum number of UDFs you have in your database? (Just curious.)

Answer 1

Some of this question really veers into heavily opinion based information, but here's what I'd say (trying to stay away from personal opinion):

1. The negative is that it's a herculean task ;)
2. No. Stored procedures can do everything functions can do and more. User Defined Functions can really be seen as a subset of stored procedures at this point.
3. I can't find what the absolute max is, but I try to avoid functions because they are very easy to get wrong and use improperly (in performance killing ways).

As an aside, have you tried using WITH SCHEMABINDING on your function definitions? This can help performance if it's applicable.

Answer 2

Well this is a complicated question with no precise answer (maybe you should post this on Quora) but i will try...

1. Besides the fact that you can't SELECT * FROM MyStoredProcedure I don't see any negative... On the other hand, you might get security, because SP must be invoked by EXEC mySP while USF not, so if a dev name a USF something like "LastYearOrders" another one might call it thinking it is a table... it also makes a cracker job a bit easier...
2. I dont think so...
3. I have none