How to make your SQL scalar user-defined function (UDF) inlineable in Microsoft Fabric Warehouse
In our previous blog post Inline Scalar user-defined functions in Microsoft Fabric Warehouse (Preview) we have announced the availability of SQL native scalar UDFs. We also emphasized the importance of inlining and how that can affect scenarios in which UDF can be used.
In this post, we aim to highlight common patterns that prevent inlining and offer suggestions on how you can rewrite your code to enable more use cases. We acknowledge that this is not a comprehensive list and that some of the techniques described here may not be applicable to all scenarios.
First let’s remember in which cases scalar UDF can’t be inlined
There are three main reasons:
- The UDF body contains constructs that prevent inlining.
- The UDF is used in unsupported parts of the query.
- The UDF cannot be inlined during distributed query execution.
While reason (1) is self-explanatory, reason (2) and (3) refer to scalar UDFs which are nominally inlineable, but user query fails. Continue on for more details.
The UDF body prevents inlining
Typical body shapes which cannot be inlined are:
- WHILE loop(s)
- Multiple RETURN statements.
- Calls to non-deterministic functions.
- References to intrinsic functions that might alter the results when inlined (such as @@ROWCOUNT).
- Use of NEWID is not allowed within a UDF.
- CTE(s)
The UDF is used in unsupported parts of the query
While UDF eligible for inlining is prerequisite to use such function in combination with a User table (SELECT … FROM query shape), there are still cases where inlining is not possible, such as:
- UDF is called explicitly in a GROUP by clause.
- UDF is called explicitly in an ORDER by clause.
The UDF cannot be inlined during distributed query execution
Typically, this is an edge case that can occur when a user query plan becomes too complex after inlining. One possible reason for this could be the complexity of the query itself.
- Output of multiple UDFs is used to create single expression.
- There are more than a dozen UDF calls in a single query.
How to check is function inlineable
Use this query for a sanity check and aim to achieve is_inlineable equal to 1.
SELECT SCHEMA_NAME(o.schema_id) as function_schema, OBJECT_NAME(m.object_id) as function_name, is_inlineable
FROM sys.sql_modules as m
JOIN sys.objects as o on m.object_id=o.object_id
WHERE SCHEMA_NAME(o.schema_id)='your_function_schema'
AND OBJECT_NAME(m.object_id)='your_function_name'
GOHow to make non-inlineable function inlineable
All these methods need a rewrite of a function code.
Multiple return statements
Tip: Consider adding a local variable(s) to keep the result and returning only final variable value.
For example, in this case we have a function which is returning different discount rates depending on Customer Type.
CREATE OR ALTER FUNCTION dbo.GetDiscount (@CustomerType VARCHAR(50))
RETURNS DECIMAL(5, 2)
AS
BEGIN
IF @CustomerType = 'Regular'
RETURN 0.05;
IF @CustomerType = 'Premium'
RETURN 0.10;
IF @CustomerType = 'VIP'
RETURN 0.15;
RETURN 0.01; -- Default discount for unknown customer types
END;
GO Consider an alternative approach to avoid multiple returns.
CREATE OR ALTER FUNCTION dbo.GetDiscount (@CustomerType VARCHAR(50))
RETURNS DECIMAL(5, 2)
AS
BEGIN
DECLARE @Discount DECIMAL(5, 2);
SET @Discount = CASE
WHEN @CustomerType = 'Regular' THEN 0.05
WHEN @CustomerType = 'Premium' THEN 0.10
WHEN @CustomerType = 'VIP' THEN 0.15
ELSE 0.01
END;
RETURN @Discount;
END;
GO Use of GETDATE() and similar non-deterministic datetime functions
Tip: Consider passing GETDATE(), and similar functions, as input parameters to a scalar UDF.
For example, in this case we have a function which is returning the current UTC date in a certain text format.
CREATE OR ALTER FUNCTION dbo.FormatCurrentDateTime()
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @CurrentDateTime DATETIME;
SET @CurrentDateTime = GETUTCDATE();
RETURN CONVERT(VARCHAR(50), @CurrentDateTime, 120); -- Format as 'yyyy-mm-dd hh:mi:ss'
END;
GO Consider an alternative approach to avoid explicit calls of non-determinant functions within the body.
CREATE OR ALTER FUNCTION dbo.FormatCurrentDateTime(@InputDateTime DATETIME2)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN CONVERT(VARCHAR(50), @InputDateTime, 120); -- Format as 'yyyy-mm-dd hh:mi:ss'
END;
GO Use of NEWID()
Tip: Consider passing NEWID as input parameter to a scalar UDF. Notice that nowadays NEWID cannot be used directly in the body.
For example, instead of:
CREATE OR ALTER FUNCTION dbo.GetNewID()
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @NewID UNIQUEIDENTIFIER;
SET @NewID = NEWID();
RETURN CONVERT(VARCHAR(50), @NewID);
END;
GO Consider passing NEWID() as input parameter:
CREATE OR ALTER FUNCTION dbo.GetNewID(@NEWID UNIQUEIDENTIFIER)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN CONVERT(VARCHAR(50), @NewID);
END;
GO Use of @@ROWCOUNT
Tip: Use local variables and other supported TSQL constructs.
For example, in this case we have a function which is doing a check does product exists in a table:
CREATE OR ALTER FUNCTION dbo.CheckProductExists(@ProductId INT)
RETURNS BIT
AS
BEGIN
DECLARE @Exists BIT;
-- Simple check if rows are affected
SELECT @Exists=1
FROM Products
WHERE ProductID = @ProductId;
IF @@ROWCOUNT > 0
SET @Exists = 1;
ELSE
SET @Exists = 0;
RETURN @Exists;
END;
GO Consider an alternative approach to avoid ROWCOUNT.
CREATE OR ALTER FUNCTION dbo.CheckProductExists(@ProductId INT)
RETURNS BIT
AS
BEGIN
DECLARE @Exists BIT;
-- Check existence using EXISTS
IF EXISTS (SELECT 1 FROM Products WHERE ProductID = @ProductId)
SET @Exists = 1;
ELSE
SET @Exists = 0;
RETURN @Exists;
END;
GO UDF contains many IF/THEN/ELSE blocks
Tip: Consider rewriting this logic to a single CASE WHEN statement.
In case inlineable scalar UDF is failing in a query
We’ll use previously created inlineable version of the GetDiscount scalar UDF to explain these scenarios. The same principles apply to any inlineable scalar UDF. We’ll also create a compact Customer table to demonstrate behaviors.
Setup
-- Create Customer table, and load few examples
-- Create the Customer table
CREATE TABLE Customer (
CustomerID INT NOT NULL,
CustomerName VARCHAR(100),
CustomerType VARCHAR(50)
);
GO
-- Insert sample data into the table
INSERT INTO Customer (CustomerID, CustomerName, CustomerType) VALUES
(1, 'Alice Johnson', 'Regular'),
(2, 'Bob Smith', 'Premium'),
(3, 'Charlie Lee', 'VIP'),
(4, 'Diana Prince', 'Type0'),
(5, 'Evan Davis', 'VIP'),
(6, 'Fiona Green', 'Premium');
GO
-- Verify the inserted data
SELECT * FROM Customer;
GO
-- Create scalar UDF
CREATE OR ALTER FUNCTION dbo.GetDiscount (@CustomerType VARCHAR(50))
RETURNS DECIMAL(5, 2)
AS
BEGIN
DECLARE @Discount DECIMAL(5, 2);
SET @Discount = CASE
WHEN @CustomerType = 'Regular' THEN 0.05
WHEN @CustomerType = 'Premium' THEN 0.10
WHEN @CustomerType = 'VIP' THEN 0.15
ELSE 0.01
END;
RETURN @Discount;
END;
GO
UDF called in an ORDER BY
Tip: Consider using column order, or column alias instead of explicit UDF call in an ORDER BY.
In case you need to order by a function output, instead of this query shape:
SELECT CustomerID, CustomerName, dbo.GetDiscount (CustomerType) as DiscountRate
FROM Customer
ORDER BY dbo.GetDiscount (CustomerType)
GO Consider using any of the following:
-- Option 1 – Via column order
SELECT CustomerID, CustomerName, dbo.GetDiscount (CustomerType) as DiscountRate
FROM Customer
ORDER BY 3
GO
-- Option 2- Via column alias
SELECT CustomerID, CustomerName, dbo.GetDiscount (CustomerType) as DiscountRate
FROM Customer
ORDER BY DiscountRate
GO UDF called in a GROUP BY
Tip: Consider doing GROUP BY in an outer query.
In case your query pattern requires group by a function output, for example:
SELECT dbo.GetDiscount (CustomerType) as DiscountRate, count(*) as NumberOfRows
FROM Customer
GROUP BY dbo.GetDiscount (CustomerType)
GO Consider enabling this scenario by appplying GROUP BY on an outer query.
SELECT DiscountRate, count(*) as number_of_rows
FROM
(
SELECT dbo.GetDiscount (CustomerType) as DiscountRate
FROM Customer
)_outer
GROUP BY DiscountRateQuery contains CTE and call to a UDF
In this case, alternative is to replace CTE with different query shape where possible.
For example:
WITH CustomerDiscounts AS (
SELECT
CustomerID,
CustomerName,
CustomerType,
dbo.GetDiscount(CustomerType) AS Discount
FROM Customer
)
SELECT * FROM CustomerDiscounts
ORDER BY Discount DESC;
GO Consider rewriting as:
SELECT
CustomerID,
CustomerName,
CustomerType,
dbo.GetDiscount(CustomerType) AS Discount
FROM Customer
ORDER BY Discount DESC;
GO There is an expression based on many UDF calls
Tip: Consider creating separate query column for each individual UDF call and doing an expression in an outer query.
For example – instead of:
SELECT udf1 + udf2 + …+ udfn as Result FROM TableUse:
SELECT udf1_output+ud2_output+…+udfn_output as Result
FROM
(
SELECT udf1 as udf1_output, ud2 as udf2_output,…, udfn as udfn_output
FROM Table
)_outer Next steps
This capability is available now in preview. To learn more about it, refer to the official documentation Create function in Microsoft Fabric Data Warehouse.
Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community.
In case you have specific questions, suggestion or scenario, email Fabric DW functions feedback.
