Scalar UDF Inlining

Question

https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sql-server-ver15

what does the follow statement means ?
Interpreted execution: Each statement itself is compiled, and the compiled plan is cached. No cross-statement optimizations are carried out.

Answer 1

If you have multi statement UDF, each statement in the function will be compiled separately and then cached as compiled plan. That means, there will not be having any optimization carried out cross statement in this case. This is one of the main reason for poor performance of UDF.

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
Blog
LinkedIn

Answer 2

Take this query:

WITH OrderAmounts AS (
   SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
   FROM   dbo.[Order Details] OD
   GROUP  BY OD.OrderID
)
SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   OrderAmounts OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;

Logically, we are saying "Compute the sum of all orders in the database and join that result to the Orders table". But most likely the optimizer will see that is sufficient to compute the some for the orders on the given day.

Now consider:

CREATE TABLE #OrderAmounts (OrderID int           NOT NULL PRIMARY KEY,
                            Amount  decimal(20,2) NOT NULL);

INSERT #OrderAmounts (OrderID, Amount)
   SELECT OD.OrderID, SUM(OD.Quantity * OD.UnitPrice) AS Amount
   FROM   dbo.[Order Details] OD
   GROUP  BY OD.OrderID;

SELECT O.OrderID, C.CustomerID, C.CustomerName, C.City, OA.Amount
FROM   dbo.Orders O
JOIN   dbo.Customers C ON O.CustomerID = C.CustomerID
JOIN   #OrderAmounts OA ON O.OrderID = OA.OrderID
WHERE  O.OrderDate = '19970421'
ORDER BY O.OrderID;

Logically, this is perfectly equivalent to the query above. However, since SQL Server does not perform optimizations across table, this time the totals for all orders will be computed and stored in the temp table. But it would be perfectly legal if it decided to ignore the temp table and shortcut in the same way as in the CTE query.

Answer 3

Hi,

A Scalar function is evaluated as a batch of statements where each statement is run sequentially one statement after another. Because of this, each statement has its own execution plan and is run in isolation from the other statements in the UDF, and therefore can’t take advantage of cross-statement optimization. Refer to this PDF document.

Best regards,
Cathy