Inline Scalar user-defined functions (UDFs) in Microsoft Fabric Warehouse (Preview)
SQL native Scalar user-defined functions (UDFs) in Microsoft Fabric Warehouse and SQL analytics endpoint are now in preview.
A scalar UDF is a custom code implemented in T-SQL that accepts parameters, performs an action such as complex calculation, and returns a result of that action as a single value.
They can contain local variables, calls to other UDFs, calls to built-in functions, control flow statements such as IF/THEN/ELSE, multiple BEGIN END blocks, as well as SELECT statements over user tables, views and inline table-valued functions.
They cannot change the state of a database, thus DML statements such as UPDATE/INSERT/DELETE over tables and views are not supported within a scalar UDF body.
Benefits of scalar UDFs?
Scalar User-Defined Functions (UDFs) provide an efficient solution for promoting code reusability across T-SQL queries. This allows data engineers to develop, test, release, and maintain their Data Warehousing solutions in a modular fashion. By using UDFs, queries become cleaner and easier to understand. They ensure that the logic is consistently applied across all applications that interact with the Warehouse, reducing the risk of discrepancies and errors.
Additionally, they enable data organizations to centralize their business logic and share it effectively across various teams within the company.
Are Scalar UDFs providing the same capabilities in Warehouse and SQL analytics endpoint?
Yes, scalar UDF provides the same benefits for both types of data items in Fabric, while respecting their nuances (e.g. it’s expected that scalar UDF can be invoked in a DML statement in Warehouse, and not in SQL analytics endpoint).
Types of Scalar UDFs in Fabric Warehouse
Based on whether they reference a table, view, or table-valued function, scalar UDFs can be categorized accordingly.
- Scalar UDFs with data access.
- Scalar UDFs without data access.
UDFs can be categorized based on their eligibility for inlining into user queries as follows:
- Inlineable scalar UDFs.
- Non-inlineable scalar UDFs.
Different types of scalar UDFs provide different sets of capabilities for an end user. To learn more, refer to the official documentation Create function in Microsoft Fabric Data Warehouse.
Why is the inlineability of scalar UDFs important?
Scalar UDFs in Fabric Warehouse are leveraging inlining, for more information refer to the Scalar UDF Inlining documentation. With this feature, scalar UDFs are automatically transformed into scalar expressions or scalar subqueries that are substituted in the calling query in place of the UDF operator. These expressions and subqueries are then optimized and sent to execution within Fabric Warehouse engine, leveraging its continued performance improvements.
Non-inlineable scalar UDF
A User can create any scalar UDF which is supported by TSQL syntax in an UDF.
In case UDF body contains constructs, which can NOT be inlined, such UDF is still valid and can be used in limited number of scenarios.
Scenarios supported for non-inlineable UDFs are:
- Call UDF as a standalone, e.g., to pass through as variable in your T-SQL batch, ETL pipeline, Report, or to provide a KPI for your Dashboard.
DECLARE @in INT = 1;
DECLARE @out INT;
SET @out = dbo.myfunction(@in)
SELECT @out as '@function_output';
- Call UDF in combination with sys objects and views (true only in case there is no reference to table/view/iTVF within function body).
-- Example 1
SELECT name, object_id, dbo.myfunction (object_id) as function_output
FROM sys.objects
-- Example 2
SELECT name, dbo.myfunction (s.object_id) as function_output
FROM sys.objects s
WHERE name like '%my%'
GROUP BY name, dbo.myfunction (s.object_id)
Typical examples of non inlineable UDF body are constructs containing WHILE LOOP(s), MULTIPLE RETURN statements, CTEs, or calls to non-deterministic built-in functions (such as GETUTCDATE). For other cases where inlining is not supported, refer to the official documentation Interoperability of functions in Fabric Data Warehouse.
Does the inlining requirement depend on if the UDF body references a table?
Currently, the same constructs prevent inlineability whether the UDF is pure expression-based or references user data. UDFs that reference a table, view, or iTVF may be more challenging to inline, which can result in differences in performance, fewer function calls allowed in a query, or rare cases of failures when the query plan is overly complex.
Supported Scenarios for Scalar UDFs
Depending on the type of scalar UDF,
| Scenario | Inlineable |
Non inlinable |
Function Creation/Alter/Drop |
Supported |
Supported |
| Function Execution | ||
Standalone call |
Supported |
Supported |
| In combination with SQL system objects (all query shapes, WHERE/JOIN/GROUP/ORDER BY) |
Supported |
Supported |
In a SELECT … FROM statement |
Supported |
Not supported |
In a WHERE clause |
Supported |
Not supported |
In a JOIN |
Supported |
Not supported |
In a GROUP BY clause |
Not supported |
Not supported |
In an ORDER BY alias or column order |
Supported |
Not supported |
In ORDER BY invoking scalar UDF |
Not supported |
Not supported |
In combination of DISTINCT and ORDER BY |
Not supported |
Not supported |
In combination with Common Table Expression (CTE) |
Not supported |
Not supported |
In INSERT/UPDATE/DELETE (while respecting above) |
Supported |
Not supported |
Conclusion
Scalar user-defined functions in Fabric Warehouse and SQL analytics endpoint is powerful feature which allow developers and analysts to encapsulate complex logic into reusable code via common TSQL language. Scalar UDF inlining plays a critical role in understanding scenarios in which a scalar UDF can be used within a user query. This approach allows scalar UDFs to run in a fully distributed manner, leveraging all innovation coming from underlying engine, try them today.
Next steps
We are actively working on enabling additional scenarios for scalar UDFs. Stay tuned for more updates. Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community.
