Buy @ Amazon

User Defined Functions in SQL Server

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  textntextimagecursor, 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.