How to make your SQL scalar user-defined function (UDF) inlineable in Microsoft Fabric Warehouse

[SMATIN]

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:

1. The UDF body contains constructs that prevent inlining.
2. The UDF is used in unsupported parts of the query.
3. 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' 
GO

How 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 DiscountRate

Query 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 Table

Use:

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.