As a developer when we have to perform complex work in stored procedure many of us use user defined functions to centralize complex logic and get a proper result, but we all know when we use user-defined functions we get stuck with a performance problem. With MS SQL 2019 preview now we got better performance with UDF inlining. A developer I am happy that MS SQL now has this great improvement in MS SQL 2019.

So in this post, I am trying to guide who is using UDF as well as who is starting work on MS SQL as developer or DBA too. So starting with the basic definition what is User Defined Functions.

What is User Defined Functions?

A UDF is a SQL statement (T-SQL) which returns a single data value is termed as Scalar UDF. UDF helps in building a complex logic without writing complex SQL queries.

Why Performance of Scalar UDF is poor some times,

• Iterative invocation
• LAck of costing
• Interpreted execution
• Serial execution

To improve this now we have automatic inlining of UDF

This new feature of auto inlining, our UDFs are automatically get transformed into a scalar expression or scalar subqueries and substituted in the calling query in place of the UDF operator.

But to have any auto inlining our UDF must fallow some requirement as per docs.microsoft.com

• A scalar T-SQL UDF can be inline if all of the following conditions are true:
  • The UDF is written using the following constructs:
  • DECLARE, SET: Variable declaration and assignments.
  • SELECT: SQL query with single/multiple variable assignments1.
  • IF/ELSE: Branching with arbitrary levels of nesting.
  • RETURN: Single or multiple return statements.
  • UDF: Nested/recursive function calls2.
  • 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 reference a scalar UDF call in its 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.

Even we can check whether our UDF can be inlined or not By using sys.sql_modules

The result shows is it can be inlined or not. A value of 1 indicates that it is inalienable, and 0 indicates for not.

How to Enable scalar UDF inlining

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

After running this query we do not have to make any changes in our works, which is great.

How to Disable Scalar UDF inlining without changing the compatibility level

 

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

MS SQL 2019 seems well improved for performance as well as well supported for the docker and Linux, which is great if we want to use ms SQL 2019 in Linux and DevOps during development. This post is just for showing interesting features of MS SQL 2019, I am doing some tests on a few features will post them soon too. Also please visit What's new in SQL Server 2019 preview to know more about.