I have been pretty excited about the new SQL 2019 feature “Scalar
UDF inlining”. I want to share what I learned about Scalar UDF inlining because it's awesome. It makes
functions easier to implement without becoming a plague to performance, thus functions will be used more often (finally).
Scalar UDF inlining in SQL 2019 simply makes most
(non-inlined) UDF’s execute faster. The SQL 2019 query engine interprets the
scalar UDF’s logic and makes it set-based (inlined), ready to take advantage of
the performance improvements that come with set based processing.
So how do we know if an existing function would be able to
use this new feature?
In my opinion, the easiest way to know if your function is able to take advantage of this is to simply query the sys.sql_modules view (SQL 2019). The sys.sql_modules catalog view includes a property called “is_inlinable”, which indicates whether a UDF is inlinable or not.
· A value of 1 indicates that it is inlinable, and 0 indicates otherwise
· This property will also have a value of 1 for inline table-valued functions, since they are inlinable by definition.
In my opinion, the easiest way to know if your function is able to take advantage of this is to simply query the sys.sql_modules view (SQL 2019). The sys.sql_modules catalog view includes a property called “is_inlinable”, which indicates whether a UDF is inlinable or not.
· A value of 1 indicates that it is inlinable, and 0 indicates otherwise
· This property will also have a value of 1 for inline table-valued functions, since they are inlinable by definition.
In my next blog post I will cover how to manually change scalar
UDFs into an inlinable (for those that won’t be going to 2019 any time soon).
Reference:
Microsoft has a large list of requirements that let you know if a Scalar UDF can be inlined:
Microsoft has a large list of requirements that let you know if a Scalar UDF can be inlined:
A
scalar T-SQL UDF can be inline if all of the following conditions are true:
·
The UDF is written
using the following constructs:
o DECLARE, SET: Variable declaration
and assignments.
o SELECT: SQL query with
single/multiple variable assignments1.
o IF/ELSE: Branching with
arbitrary levels of nesting.
o RETURN: Single or multiple
return statements.
o UDF: Nested/recursive
function calls2.
o Others: Relational operations such as EXISTS, ISNULL.
·
The UDF does not
invoke any intrinsic function that is either time-dependent (such as GETDATE()) or has side effects3 (such as NEWSEQUENTIALID()).
·
The UDF uses the EXECUTE AS CALLER clause (the default behavior if the EXECUTE AS clause is not
specified).
·
The UDF does not
reference table variables or table-valued parameters.
·
The query invoking a
scalar UDF does not reference a scalar UDF call in its GROUP BY clause.
·
The query invoking a
scalar UDF in its select list with DISTINCT clause does not have ORDER BY clause.
·
The UDF is not used
in ORDER BY clause.
·
The UDF is not
natively compiled (interop is supported).
·
The UDF is not used in
a computed column or a check constraint definition.
·
The UDF does not
reference user-defined types.
·
There are no
signatures added to the UDF.
·
The UDF is not a
partition function.
1 SELECT with variable
accumulation/aggregation (for example, SELECT
@val += col1 FROM table1) is not supported for
inlining.
2 Recursive UDFs
will be inlined to a certain depth only.
3 Intrinsic
functions whose results depend upon the current system time are time-dependent.
An intrinsic function that may update some internal global state is an example
of a function with side effects. Such functions return different results each
time they are called, based on the internal state.
