how to see the effect of Enable Scalar UDF Inlining in query plan

Question

hi,

i read this artical https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver16

but could not reproduce  the following results , is ther any query or example on interent where i can see the following in action.

 

Check whether inlining has happened or not

If all the preconditions are satisfied and SQL Server decides to perform inlining, it transforms the UDF into a relational expression. From the query plan, it's easy to figure out whether inlining has happened or not:

The plan xml won't have a <UserDefinedFunction> xml node for a UDF that has been inlined successfully.

Certain XEvents are emitted.

Answer 1

Here is an example you can play with. First some tables:

CREATE TABLE FruitTable (FruitID    int            NOT NULL,
                         FruitName  nvarchar(30)  NULL,
     CONSTRAINT pk_FruitTable PRIMARY KEY (FruitID)
)
INSERT FruitTable(FruitID, FruitName)
   VALUES (1, N'Pinapple'),
          (2, N'Pear'),
          (3, N'Mango'),
          (4, N'Red currant'),
          (5, N'Raspberry'),
          (6, N'Passion fruit')
go
CREATE TABLE Orders (OrderID   int  NOT NULL,
                     OrderDate date NOT NULL,
                     FruitID   int  NOT NULL,
                     CONSTRAINT pk_Orders PRIMARY KEY(OrderID)
)
INSERT Orders(OrderID, OrderDate, FruitID)
   VALUES(1, '2022-11-11', 2),
         (2, '2023-09-09', 6),
         (3, '2024-07-01', 1)

Then a function (not that you should ever write functions like this):

CREATE FUNCTION GetFruit (@fruitid int) RETURNS nvarchar(30) AS
BEGIN
   RETURN (SELECT FruitName 
           FROM   FruitTable 
           WHERE  FruitID = @fruitid)
END

Then run these query with display of execution plan enabled:

SELECT O.OrderID, O.OrderDate, FriuitName = dbo.GetFruit(O.FruitID)
FROM   Orders O
go
SELECT O.OrderID, O.OrderDate, FriuitName = dbo.GetFruit(O.FruitID)
FROM   Orders O
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))

You will see that the plans are quite different. In the plan for the second query, the function call is hidden in the Compute Scalar operator.