- Advantages of UDFs
- UDF Limitations
- Misuses of UDFs
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
UDF Limitations
Because UDFs are new in SQL Server 2000, there are many debates about when you should use a UDF as opposed to a stored procedure. There is no short simple answer for this question. Everything you can do in a UDF can be done with a stored procedure. The reverse is not true, however—stored procedures allow much more flexibility than UDFs. Let's examine some of the limitations of UDFs that prevent them from being as effective as stored procedures.
No Side Effects
As we mentioned in the previous article, UDFs are not allowed to have side effects on data: They cannot modify data in permanent tables; they can only read data. Nor can you make structural changes to the permanent tables within a UDF. The only exception to this rule is that UDFs can call extended stored procedures, which in turn can modify the data or structure of the permanent tables.
Keep in mind, however, that due to the power of extended stored procedures, typically only members of SYSADMIN role have permission to execute them. If you need to make changes to the data, it is easier to use a stored procedure. Note also that extended stored procedures used within a UDF cannot return data to the client.
No Use of Non-deterministic Built-in Functions
UDFs cannot use non-deterministic built-in functions. This rule prohibits usage of very common functions such as GETDATE() or RAND() in UDFs.
Smaller Number of Parameters
UDFs can accept a smaller number of parameters than stored procedures. UDFs can have up to 1024 parameters, whereas stored procedures support up to 2100 parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters.
Limitation of Using Cursors
UDFs cannot return data to the user through the FETCH keyword within a cursor. This limitation is also minor because you can populate a table variable within a cursor and then return the table to the user.
Returns Only One Result Set
UDFs can return only one rowset to the user, whereas stored procedures can return multiple rowsets. Due to this limitation, the majority of UDFs can't be used for populating user interface screens requiring outputs from multiple queries. You can call multiple UDFs as a workaround for this limitation.
Cannot Call Stored Procedures
UDFs cannot call stored procedures (except extended procedures), whereas stored procedures can call other procedures. This is noteworthy, because UDFs must be self sufficient to a degree; most of UDF logic must be done within the UDF or another UDF—a function can call another function.
Cannot Execute Dynamic SQL
UDFs also cannot execute dynamically constructed SQL statements. If you need to construct a statement dynamically based on the parameter values, you must resort to using stored procedures.
Cannot Change SET Options
SQL Server has numerous connection-level options that can be altered by using various SET statements. SET options can affect not only the performance of the queries, but their output as well. SET options cannot be altered within UDFs.
Cannot Return XML
UDFs cannot return XML string as output; you cannot use the FOR XML clause in SELECT queries within UDFs.
Cannot Use Temporary Tables
UDFs cannot make use of temporary tables. As an alternative, you are allowed to use table variables within a UDF. Recall however, that temporary tables are somewhat more flexible than table variables. The latter cannot have indexes (other than a primary and unique key); nor can a table variable be populated with an output of a stored procedure.
Limited Error Handling
RAISERROR statement cannot be used within a UDF. In fact, you can't even check the value of the @@ERROR global variable within a function. If you encounter an error, UDF execution simply stops, and the calling routine fails. You are allowed to write a message to the Windows error log with xp_logevent if you have permission to use this extended procedure.