I have witnessed many different interviews and the matter of fact, one of the services I provide is where I help organizations identify various right candidates while interviewing them. I always prefer to do interviews along with members of the team who are willing going to work with candidates as that gives a clear idea to everybody about the work environment and colleagues. One of the questions which I keep on hearing again and again is the following question. I believe it is a very old question and most of the candidates knows the answer of it. However, this is still very popular question in my organizations and it is about User Defined Functions.
Question: What are the limitation of the User Defined Functions (UDF)?
Answer:
The UDF has its own advantage and usage, but in this article we will see the limitation of UDF. Things UDF cannot do and why Stored Procedure are considered as more flexible than UDFs. Stored Procedure are more flexible than User Defined Functions (UDF).
- The UDF has No Access to Structural and Permanent Tables.
- UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
- UDF Accepts Lesser Numbers of Input Parameters.
- UDF can have up to 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
- UDF Prohibit Usage of Non-Deterministic Built-in Functions
- Functions GETDATE () etc. cannot be used UDFs, but can be used in Stored Procedure
- UDF Returns Only One Result Set or Output Parameter
- Due to this it can be used in a SELECT statement, but can not return multiple result set like Stored Procedure
- UDF cannot Call Stored Procedure
- Only access to Extended Stored Procedure.
- UDF cannot Execute Dynamic SQL, or Temporary Tables
- UDF cannot run dynamic SQL which is dynamically building in UDF. Temporary Tables can not be used in UDF as well.
- UDF cannot Return XML
- FOR XML is not allowed in UDF
- UDF does not support SET options
- SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
- UDF does not Support Error Handling
- RAISEERROR or @@ERROR are not allowed in UDFs.
As I said earlier in this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it, I can do a repetitive task in SELECT statement as well as modularizing I frequently used code.
Reference : Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Pinal, can you verify that are all of these answers still valid?
I didn’t believe the bit about using non-deterministic functions, and was able to create a scalar-valued function that returns the result of GETDATE() as well as permanent table data just fine.
Also, the answer ignores the existence of table-valued functions, which can be used to return more than one row (as well as use in cross apply!). It may be an old question, but unless I’m misunderstanding the question, the answers appear out of date!
“FOR XML is not allowed in UDF”
That is not true. I have functions where I use FOR XML PATH(”) in order to create CSV lists from values in a column from a set.
One thing that UDF cannot do compared to stored procedures is to omit a parameter that it has a default value. You have to at least use NULL for the parameter.
i have tried to create function with more then 1023 parameter and function name as GETDATE but it’s working for me.
Thanks for sharing jignesh.
I used a lot GETDATE() in my functions