0

What is inlining in sql server? How is ITVF better than MTVF?

RobMartin
  • 49
  • 5
  • have youtried the documentation https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15 – nbk Oct 28 '21 at 15:49

1 Answers1

0

SQL Server 2019 introduced the ability to inline Scalar user-defined functions.

UDFs are good for encapsualting logic and code-reuse however for query processing thay have traditionally incurred a performance overhead due to the nature of how they have to be implemented and prevent query execution from going parallel, since the queries within the UDF may also require parallelism.

When invoking a UDF, SQL Server encapsulates its logic in a "black box" and invokes it repeatedly, row by row, for each row in the query.

Thanks to work carried out at Microsoft Gray Systems lab, a generalised process known as FROID has been utilised in SQL Server 2019 which overcomes this drawback by analyzing the UDF to transform an entire multi-statement function into the equivalent relational algebraic expression.

This allows the optimizer to implement its logic as part of an apply function where it supports set-based optimal processing and can benefit from parallelism and can make a previously RBAR function perform orders of magnitude faster.

Stu
  • 30,392
  • 6
  • 14
  • 33