Some tips for using User-Defined Functions in SQL Server 2016
Use scalar user-defined functions when you need to do the same mathematical
calculations at multiple places in code.
For example, if you need to calculate the factorial for the given number, you can write
appropriate scalar user-defined function to encapsulate code for reuse.
Consider using CLR user-defined functions.
The CLR user-defined functions were first introduced in SQL Server 2005. You can write the
user-defined functions in any .NET Framework programming language, such as Visual Basic .NET
or Visual C#.
Try to avoid using nested user-defined functions.
Because using nested user-defined functions can result in some performance degradation, try to
break down a nested function into simpler functions.
Consider using user-defined table type as a parameter for user-defined function (UDF).
SQL Server 2016 supports a user-defined table type that supports representing table structures
for use as parameters in user-defined function. Using user-defined table type can simplify
user-defined function’s programming and may increase UDF performance.
Use TRY-CATCH logic to trap and handle error conditions in the user-defined functions.
You can enclose a group of Transact-SQL statements in a TRY block and if error occurs within
this block, control will be passed to a CATCH block. If there are no errors in a TRY block
control will be passed to the statement immediately after the associated END CATCH statement.
TRY and CATCH blocks can be nested, each of them can contain nested TRY and CATCH blocks.
Consider breaking down a complex user-defined function into simpler functions and
use the simpler functions together to produce the result.
By using so, you can reuse the code produced by complex user-defined function in more
places in the application.
If you want to invoke a stored procedure directly from a query and this stored
procedure returns a scalar value, consider replacing a stored procedure with a
scalar user-defined function.
A stored procedure cannot be invoked from the SELECT statement, but if you need to invoke
some code inside the query, try to write the user-defined function, which perform the
same task.
Consider using inline table-valued user-defined functions (udf) or multistatement
table-valued user-defined functions instead of views.
In comparison with views, which can contain a single SELECT statement only, a user-defined
function can contain additional statements (such as DECLARE statements, cursor operations,
EXECUTE statements, and so on) that allow more powerful logic than is possible in views.
If you want to invoke a stored procedure directly from a query and this stored
procedure returns a single result set, consider replacing a stored procedure with
a table-valued user-defined function.
The table returned by a stored procedure cannot be referenced in the FROM clause of a
Transact-SQL statement, whereas the table returned by a user-defined function can, but
you cannot replace all stored procedures that return a single result set with the udf.
To replace a stored procedure
with a table-valued user-defined function, the following conditions should be met:
– the stored procedure does not perform update operations (except to table variables)
– the stored procedure does not contain dynamic EXECUTE statements
– the stored procedure returns one result set
Consider creation a user-defined function with the SCHEMABINDING clause.
Using CREATE FUNCTION statement with the SCHEMABINDING clause guarantees that the
database objects that a user-defined function references cannot be altered or dropped.
Try to avoid calling extended stored procedures from within the user-defined functions.
User-defined functions that call extended stored procedures are considered nondeterministic
and can cause side effects. If a user-defined function has side effects on the database,
do not rely on a consistent result set or execution of this function.
Consider using native compiled scalar user-defined functions.
In SQL Server 2016 scalar user-defined functions can now be natively compiled. By using
natively compiled scalar udf you can improve total udf performance.
Use the user-defined functions in dynamic filters to define different partitions
of one publication replicated to different subscribers.
You can improve dynamic filtering performance by invoking user-defined functions to
determine the different partitions of data.
Consider using the READONLY option of the CREATE FUNCTION statement.
The READONY option indicates that the parameter cannot be updated or modified within the
definition of the function. You should specify the READONLY option, if the parameter type
is a user-defined table type.
Consider creating indexed views that contain user-defined functions.
In SQL Server 2016 the indexed view definitions can also contain user-defined functions
with certain restrictions.