I always look forward to new T-SQL features and optimizer enhancements with every new version of SQL Server. Starting in 2017, Microsoft came up with a set of features called Intelligent Query Processing (IQP). These features work to improve performance without changing any code. If you’d like to learn more about these features, take a look at Microsoft’s page on the topic. There are also several articles on Simple Talk by Greg Larsen on the features.

These features improve the performance of some typical patterns; for example, one feature is inlined scalar user-defined functions (UDFs) available in 2019. Depending on the situation, this can lead to a significant performance boost without changing any code. Say you have a function that accepts a couple of parameters and then runs a query to return a sum. As long as the function meets the criteria for inlining, SQL Server treats it as if you included your code in the query instead of using a function. Some folks don’t realize that the query using a UDF in the SELECT list calls the UDF once for every row. Just think about the impact if the query inside the UDF happens to do a scan on a large table!

Just like any new feature, you must do some testing to see if it helps or causes unanticipated problems. All the IQP features can be toggled off and on by changing the database compatibility level. If you turn it below 150 (SQL Server 2019), you disable all the 2019 IQP features. If you wish to disable just one of the features, set the compatibility to 150 and then flip the switch for the database for that particular feature. Here's the command to turn off UDF inlining:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

But what if you need this feature except that you have one UDF that causes issues when inlining is turned on? For example, UDFs use deferred resolution. That means that an object doesn’t have to exist at function creation time. In a case like this, inlining the UDF could cause an error.

Here’s a simple example:

CREATE OR ALTER FUNCTION dbo.SwitchTable()
RETURNS INT AS
BEGIN
    DECLARE @Count INT;
    IF OBJECT_ID('testTable','U') IS NOT NULL BEGIN
        SELECT @Count = COUNT(*)
        FROM testTable;
    END
    ELSE BEGIN
        SELECT @Count = COUNT(*)
        FROM Production.Product;
    END
    RETURN @Count;
END;
GO
SELECT ProductID, Name, dbo.switchTable() AS productCount
FROM Production.Product;

When you run the query with scalar UDF inlining enabled, you’ll see this error if testTable doesn’t exist:

Msg 208, Level 16, State 1, Procedure SwitchTable, Line 7 [Batch Start Line 21] Invalid object name 'testTable'.

To get around this while still allowing the feature to be used for other scalar UDFs, add this hint to the query:

SELECT ProductID, Name, dbo.switchTable() AS productCount
FROM Production.Product
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

The team at Microsoft is doing some great things with SQL Server! If you use a lot of scalar UDFs, an upgrade to 2019 with scalar UDF inlining could solve some of your issues, and you have control over when to use it.