Free SQL Help for Devs, DBAs, and the Curious

Welcome to SQLSupport.org's blog! Today, we're going to delve into an important feature introduced in SQL Server 2017 - SQL Scalar Function Inlining. This new SQL Server 2017 feature builds upon the technologies present in SQL Server 2016, MySQL 5.7, DB2 11.1, and Azure SQL. The SQL Scalar Function Inlining brings in some significant performance improvements and offers a lot more flexibility when working with scalar functions.

Scalar functions are widely used in SQL programming. They accept parameters and return a single value. Prior to SQL Server 2017, scalar functions could slow down the performance of a query because they were executed row by row, not only making the processing time longer but also preventing the SQL Server query optimizer from accurately estimating the cost of the function.

The SQL Scalar Function Inlining feature in SQL Server 2017 addresses this issue by "inlining" the function into the calling query, treating it as if it were a part of the original SQL statement. This allows the query optimizer to better understand the function's behavior, which can lead to more efficient execution plans and faster query times. Here's an example of how a scalar function is defined:

CREATE FUNCTION dbo.AddNumbers(@num1 int, @num2 int)
RETURNS int
AS
BEGIN
RETURN (@num1 + @num2)
END

This simple scalar function adds two numbers. Now, we can call this function in a SQL statement, and thanks to the function inlining feature, the SQL Server 2017 optimizer will treat this function as part of the SQL statement:

SELECT dbo.AddNumbers(column1, column2) AS Result
FROM YourTable

One important aspect to note about the scalar function inlining feature in SQL Server 2017 is that it's automatically enabled when the database compatibility level is set to 150. This means that if you're upgrading from an older version of SQL Server, you might want to test your scalar functions first to avoid any potential issues.

In summary, the SQL Scalar Function Inlining feature introduced in SQL Server 2017 is a significant improvement that can greatly speed up queries that use scalar functions. It's a welcome addition to the toolbox of any SQL programmer and is a great example of how Microsoft continually improves its products to deliver the best possible performance.

If you have questions or would like to learn more about SQL Server 2017 and its features, please feel free to reach out to us. Stay tuned for more posts on SQL Server and other database technologies!