SQL Server 2019 Fixes Function Inlining Bug

Like most databases created in the last few decades, SQL Server allows developers to extend the database by creating functions. But prior to the upcoming 2019 release, SQL Server’s support for scalar functions was fraught with problems.

In SQL Server, a function that returns a single value is known as a “scalar UDF” or “scalar user defined function”. These scalar UDFs are automatically categorized as deterministic or non-deterministic by the database. An example of a non-deterministic function would be one that reads the current time or fetches data from a table. A deterministic function is guaranteed to always return the same value for a given set of parameters. In theory this means the database can apply additional optimizations when it knows a deterministic UDF is being used.

Unfortunately, SQL Server never fully integrated scalar UDF support into its execution plan generator. This means it would often perform unnecessary work such as executing a deterministic function on every row even when it was clear it would be faster to only execute it once for each unique value.

It is hard to say how often reusing previous values would actually help. Unless the data was pre-sorted or it knew there were a limited number of possible inputs, the cost of caching the function parameters and results may have outstripped the benefit. However, that’s not the only problem with scalar UDFs.

Another issue with scalar UDFs in SQL Server is they prevent parallelization. The ability to distribute complex queries across multiple CPUs is a major selling point for SQL Server. (Many of the open source alternatives barely have parallelization support or rely on distributed databases instead.) Without parallelization, many find it hard to justify SQL Server’s price.

Speaking of price, SQL Server doesn’t have a way to estimate how expensive a scalar function is. All scalar functions, no matter how simple or complex, are given a single default cost in the execution plan.

Scalar UDFs are also interpreted separately from the query that use them. Which, according to Microsoft, involves a context switch from the query to the function and back for each row. One could assume the cost of the context switch may exceed the cost of the function itself.

For these reasons, many developers and DBAs recommend against using scalar functions for performance sensitive code. Which, while understandable, often leads to a lot of code duplication as the contents of the scalar function are pasted into every query, view, and stored procedure that need it. It wouldn’t even be unusual to hear statements such as “code reuse, aside from views, is inappropriate for a database”.

The performance penalty for scalar functions cannot be overstated. Consider this simple function:

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2) AS
BEGIN
    RETURN @price * (1 - @discount);
END

According to Karthik Ramachandra, a researcher for Microsoft, this scalar function can cause a query that normally takes 1.6 seconds to instead require 29 minutes and 11 seconds. This is over 1,000 times slower with no semantic difference.

There is a workaround. Rather than using a scalar function, you may be able to use a “inline table-valued function” or “inline TVF”. A table-value function normally returns a set of rows, but it could be written to only return one. At which point the CROSS APPLY operator can be used to simulate a normal scalar function call. And as the name implies, inline TVFs are inlined and optimized as normal.

Function inlining in SQL Server 2019

Starting with SQL Server 2019, scalar functions written in T-SQL may be inlined. This means they will be folded into the query and no longer incur the UDF overhead. When looking at the execution plan, you should see no difference between a query with pasted-in logic and a query using an inlined scalar function.

This new functionality isn’t limited to simple expressions. Some multi-statement UDFs can also be inlined. This even allows inlining of non-deterministic UDFs that involve reading data from tables. It may also infer the need to add a JOIN or GROUP BY operator to combine the tables in the query with the tables in the function.

Not all functions can be inlined. Currently, the UDF needs to meet these requirements:

• 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 effects (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 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.

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.

UDF inlining can be disabled by setting the database compatibility level to less than 150 or by setting the scoped configuration named TSQL_SCALAR_UDF_INLINING to OFF.

Inlining can also be disabled for a given query using OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING')).

Finally, you can specify that a specific function should never be inlined using WITH INLINE = OFF in its declaration.

Froid and Future Opportunities

This functionality may have never been added to SQL Server without the Froid research project. Published under the title, Froid: Optimization of Imperative Programs in a Relational Database in the Proceedings of the VLDB Endowment and The 44^th International Conference on Very Large Data Bases in Rio de Janeiro, Brazil. Froid is described as,

an extensible framework for optimizing imperative programs in relational databases. Froid’s novel approach automatically transforms entire User Defined Functions (UDFs) into relational algebraic expressions, and embeds them into the calling SQL query. This form is now amenable to cost-based optimization and results in efficient, set-oriented, parallel plans as opposed to inefficient, iterative, serial execution of UDFs. Froid’s approach additionally brings the benefits of many compiler optimizations to UDFs with no additional implementation effort. We describe the design of Froid and present our experimental evaluation that demonstrates performance improvements of up to multiple orders of magnitude on real workloads.

Currently the Froid framework is only known to support T-SQL, but C#, Java, Python, and R were also mentioned in the academic paper. With SQL Server now supporting three of the four languages, it would be beneficial to extend function inlining to the other languages.