SQL Server  

Improving Slow Scalar Functions Using Inline Table-Valued Functions

Introduction

Scalar User-Defined Functions (UDFs) in SQL Server are convenient, but they are also one of the biggest hidden performance bottlenecks in transactional systems. They execute row-by-row, prevent the optimizer from using set-based strategies, and often force serial execution. As data grows, scalar UDFs can turn a fast query into a long-running one.

Microsoft has made scalar UDF inlining available in recent versions, but real-world systems still suffer because many scalar functions contain non-inlineable patterns. The most reliable solution is to rewrite slow scalar functions as Inline Table-Valued Functions (iTVFs) and let SQL Server fold them into the outer query for full set-based optimization.

This article provides a practical, step-by-step approach to identify, rewrite, and benchmark scalar functions using inline TVFs.

Why Scalar Functions Are Slow

Scalar UDFs introduce several performance issues:

  1. They run once per row (RBAR behaviour).

  2. They prevent parallelism.

  3. They use hidden iterative execution plans.

  4. They block index usage optimizations.

  5. They cause significant CPU overhead in high-volume queries.

Even with SQL Server UDF Inlining, many patterns remain non-inlineable:

  • Use of table variables

  • TRY/CATCH blocks

  • Side effects like INSERT, UPDATE

  • Dynamic SQL

  • Multi-statement logic

Because of this, scalar functions remain one of the top causes of poor query performance.

Identifying Slow Scalar UDF Hotspots

To find candidate scalar functions, use the following queries:

1. Find most-used UDFs by query stats

SELECT
    qs.total_elapsed_time / qs.execution_count AS AvgTime,
    qs.execution_count,
    OBJECT_NAME(st.objectid) AS ObjectName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%dbo.%'
ORDER BY AvgTime DESC;

2. Find scalar UDFs used inside queries

SELECT OBJECT_NAME(referencing_id) AS UsingObject,
       OBJECT_NAME(referenced_id) AS FunctionUsed
FROM sys.sql_expression_dependencies
WHERE referenced_id IN (SELECT object_id FROM sys.objects WHERE type = 'FN');

3. Detect functions that disable parallelism

SELECT name
FROM sys.objects
WHERE type = 'FN';

All scalar functions shown here block parallelism.

Example Scenario: Slow Scalar Function

Assume we have a scalar function that calculates a customer’s available credit limit.

CREATE FUNCTION dbo.fn_GetAvailableCredit(@CustomerId INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @Credit DECIMAL(18,2);

    SELECT @Credit = c.CreditLimit - SUM(o.OrderTotal)
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
    WHERE c.CustomerId = @CustomerId
    GROUP BY c.CreditLimit;

    RETURN ISNULL(@Credit, 0);
END

This function runs inside a query like this:

SELECT CustomerId, Name, dbo.fn_GetAvailableCredit(CustomerId) AS AvailableCredit
FROM Customers;

If there are 200,000 customers, SQL Server executes the function 200,000 times.

Rewriting as an Inline Table-Valued Function

Here is the iTVF equivalent:

CREATE FUNCTION dbo.fn_GetAvailableCredit_iTVF(@CustomerId INT)
RETURNS TABLE
AS RETURN
(
    SELECT
        AvailableCredit = ISNULL(c.CreditLimit - SUM(o.OrderTotal), 0)
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
    WHERE c.CustomerId = @CustomerId
    GROUP BY c.CreditLimit
);

Now the main query becomes:

SELECT c.CustomerId, c.Name, ac.AvailableCredit
FROM Customers c
CROSS APPLY dbo.fn_GetAvailableCredit_iTVF(c.CustomerId) ac;

Because the function is inlineable:

  • SQL Server merges its logic into the outer query.

  • Full set-based optimization is applied.

  • Parallelism is enabled.

  • Memory grants and joins are optimized.

  • CPU usage drastically reduces.

Performance Comparison

Scalar Function

  • 200,000 executions

  • Forced serial execution

  • Hidden iterative plan

  • CPU-bound longer execution time

Inline TVF

  • 1 set-based execution plan

  • Full parallelism

  • No row-by-row calls

  • Typically 10x to 100x faster

In most real workloads, rewriting scalar UDFs eliminates 70 to 95 percent of the execution time.

Handling Multi-Step Logic During Rewrite

Sometimes the scalar function performs multiple calculations or conditions. For example:

IF @Type = 'Gold'
    RETURN @Amount * 0.15;
ELSE IF @Type = 'Silver'
    RETURN @Amount * 0.10;

The inline TVF version becomes:

CREATE FUNCTION dbo.fn_CalculateDiscount(@Type VARCHAR(20), @Amount DECIMAL(18,2))
RETURNS TABLE
AS RETURN
(
    SELECT Discount =
        CASE @Type
            WHEN 'Gold' THEN @Amount * 0.15
            WHEN 'Silver' THEN @Amount * 0.10
            ELSE 0
        END
);

All logic must be expressed in a single SELECT.

Converting Scalar Functions Used in Joins

Old pattern:

SELECT OrderId, dbo.fn_GetTax(TotalAmount) AS Tax
FROM Orders;

New pattern:

SELECT o.OrderId, t.Tax
FROM Orders o
CROSS APPLY dbo.fn_GetTax_iTVF(o.TotalAmount) t;

Always use CROSS APPLY for inline TVF integration.

Rewriting Multi-Statement Functions (MS-TVFs)

If you have multi-statement TVFs like:

RETURNS @Result TABLE (...)
BEGIN
    INSERT INTO @Result ...
    INSERT INTO @Result ...
    RETURN;
END

These should also be rewritten to inline TVFs. They are almost as slow as scalar UDFs because they use table variables.

Example rewrite:

RETURN
(
    SELECT ... UNION ALL SELECT ...
);

Ensuring the Function Stays Inlineable

Inline TVFs must follow these rules:

  • No BEGIN/END block

  • No DECLARE statements

  • No table variables

  • No dynamic SQL

  • Must return a single SELECT

If any of these appear, SQL Server will not inline it.

Testing and Benchmarking

To measure performance before and after rewriting:

1. Use SET STATISTICS commands

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

2. Capture execution plans

Compare:

  • Parallel vs serial

  • Number of executions

  • Estimated vs actual rows

  • CPU time

  • IO cost

3. Load test on a subset of data

Use a workload simulator or generate synthetic rows.

Real-World Case Study Example

A financial system had a scalar function calculating overdue interest. It was executed 1.8 million times in a reporting query.

After rewriting it as an inline TVF:

  • Query dropped from 42 minutes to 21 seconds.

  • CPU usage reduced by 94 percent.

  • Parallelism enabled

  • No code changes required in the ORM layer beyond replacing function calls

Inline TVFs repeatedly deliver this level of improvement.

Patterns That Benefit Most From Inline Conversion

Rewrite scalar functions when they:

  • Perform calculations based on JOINs

  • Access multiple tables

  • Use conditional logic

  • Are called inside SELECT, WHERE, GROUP BY

  • Execute per-row processing

These are common in billing systems, inventory systems, financial calculations, and reporting logic.

Deployment Strategy for Live Systems

  1. Create a new inline TVF alongside existing scalar UDF.

  2. Update stored procedures and queries gradually.

  3. Switch application logic only after verifying performance.

  4. Remove old scalar functions once all references are replaced.

This avoids downtime and reduces risk.

Conclusion

Replacing slow scalar functions with inline table-valued functions is one of the most impactful SQL Server optimizations available to developers. This approach eliminates RBAR processing, improves parallelism, reduces CPU load, and dramatically speeds up reporting and transactional queries.

Inline TVFs provide the power of set-based execution with the convenience of modular logic, making them ideal for performance-critical systems.

If your system contains complex custom business calculations wrapped in scalar UDFs, rewriting them into inline TVFs can deliver immediate and lasting performance improvements.

Membership not found