My notes on User Defined Functions (
UDFs) in SQL Server are below:
- SQL Server user-defined functions are routines that accept parameters, perform an action, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
- Functions in SQL Server are of 2 types:
- System Function : are in-built functions that come bundled with SQL Sever. These functions cannot be modified.
- User Defined Function : is a function defined by a user.
- UDFs can be any of the 2 types below:
- Scalar Function : return a single data value. The return type can be any data type except text, ntext, image, cursor, and timestamp.
- Table-Valued Function (TVF) : return a table data type.
-
For an inline scalar function, the returned scalar value is the result of a single statement.
--
-
For a multi-statement scalar function, the function body can contain a series of Transact-SQL statements that return the single value.
--
- Scalar UDFs typically end up performing poorly due to the following reasons:
-
Iterative invocation: UDFs are invoked in an iterative manner, once per qualifying tuple. This incurs additional costs of repeated context switching due to function invocation. Especially, UDFs that execute Transact-SQL queries in their definition are severely affected.
-
Lack of costing: During optimization, only relational operators are costed, while scalar operators are not.
-
Interpreted execution: UDFs are evaluated as a batch of statements, executed statement-by-statement. Each statement itself is compiled, and the compiled plan is cached. Although this caching strategy saves some time as it avoids recompilations, each statement executes in isolation. No cross-statement optimizations are carried out.
-
Serial execution: SQL Server does not allow intra-query parallelism in queries that invoke UDFs. Transact-SQL UDFs in queries can only be executed on a single thread (serial execution plan). Therefore using UDFs inhibits parallel query processing. For more information about parallel query processing, see the Query Processing Architecture Guide.
- To modify a scalar function, you use the ALTER instead of the CREATE keyword. The rest of the function definition would remain the same.
- For every T-SQL scalar UDF, the sys.sql_modules catalog view includes a property called is_inlineable, which indicates whether a UDF is inlineable or not.
A value of 1 indicates that it is inlineable, and 0 indicates otherwise. This property will have a value of 1 for all inline TVFs as well. For all other modules, the value will be 0. - If a scalar UDF is inlineable, it does not imply that it will always be inlined. SQL Server will decide (on a per-query, per-UDF basis) whether to inline a UDF or not.
- If SQL Server decides to inline a UDF it converts it into a relational expression and from the query plan, it is easy to figure out whether inlining has happened or not:
- The plan xml will not have a <UserDefinedFunction> xml node for a UDF that has been inlined successfully.
- Certain XEvents are emitted.
