Using Functions in SQL Server Views: Patterns, Pitfalls, and Performance (2026)

The Joy of Discovery (and the Reality Check That Follows)

A post on r/SQLServer this week captured the feeling exactly. Titled "OMG! I Just Discovered Functions in Views and I am in Heaven", it had 28 comments and most of them were people sharing that same moment of revelation: you realize you can encapsulate a piece of business logic in a function, call it inside a view, and suddenly your queries are short, clean, and reusable. The messy date-formatting expression you were copy-pasting across fifteen stored procedures? Gone. The fiscal-year bucketing logic? Hidden behind a clean function call.

It genuinely is a powerful pattern. And then, usually a few weeks later, someone runs that view against a table with two million rows and opens a support ticket wondering why the report that used to take three seconds now takes nine minutes.

This guide is the experienced-DBA reality check the comments section didn't fully provide. Functions in views are not uniformly safe — the performance characteristics vary dramatically by function type, and one type (the scalar UDF in a SELECT list) is a well-documented performance anti-pattern that has cost teams real production downtime. Once you know which type you are using and why it matters, you can get all the encapsulation benefits without the performance surprise.

If you want to test rewritten queries as you read, open AI2SQL in a side tab — paste your T-SQL, pick SQL Server as the dialect, and get rewrite suggestions you can verify with an actual execution plan. 7-day trial, card required.

Three Kinds of Functions You Can Use in a View

SQL Server supports three types of user-defined functions, and all three can appear in a view definition. They behave very differently at runtime.

Scalar UDF

A scalar UDF takes one or more inputs and returns a single scalar value — a number, a string, a date. You call it in a SELECT list like a column expression. The function body can be anything: branching logic, string manipulation, date math, even nested function calls. The syntax is maximally flexible, which is part of why it feels so good when you first discover it.

-- Scalar UDF: returns a single value per call
CREATE FUNCTION dbo.GetFiscalYear(@OrderDate DATE)
RETURNS INT
AS
BEGIN
    RETURN CASE
        WHEN MONTH(@OrderDate) >= 10 THEN YEAR(@OrderDate) + 1
        ELSE YEAR(@OrderDate)
    END;
END;
GO

-- View calling a scalar UDF
CREATE VIEW dbo.vw_OrdersWithFiscalYear AS
SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalAmount,
    dbo.GetFiscalYear(o.OrderDate) AS FiscalYear  -- scalar UDF call
FROM dbo.Orders o;

Inline Table-Valued Function (iTVF)

An iTVF is defined as a single SELECT statement with no BEGIN/END block. It returns a table result. Because the body is exactly one SELECT, the query optimizer can expand it inline into the calling query, treating it as a derived table. The optimizer sees through it and can apply indexes, parallelism, and predicate pushdown as if the function body were written directly in the query.

-- Inline TVF: single SELECT, no BEGIN/END
CREATE FUNCTION dbo.GetOrdersForCustomer(@CustomerID INT)
RETURNS TABLE
AS
RETURN (
    SELECT
        o.OrderID,
        o.OrderDate,
        o.TotalAmount,
        CASE
            WHEN MONTH(o.OrderDate) >= 10 THEN YEAR(o.OrderDate) + 1
            ELSE YEAR(o.OrderDate)
        END AS FiscalYear
    FROM dbo.Orders o
    WHERE o.CustomerID = @CustomerID
);
GO

-- View using an iTVF with CROSS APPLY
CREATE VIEW dbo.vw_CustomerOrders AS
SELECT
    c.CustomerID,
    c.CustomerName,
    f.OrderID,
    f.OrderDate,
    f.TotalAmount,
    f.FiscalYear
FROM dbo.Customers c
CROSS APPLY dbo.GetOrdersForCustomer(c.CustomerID) f;

Multi-Statement Table-Valued Function (mTVF)

An mTVF has a BEGIN/END block, declares a return table variable, populates it with multiple statements, and returns it. This flexibility comes at a cost: the optimizer cannot see inside the function body. It treats the return table as an opaque black box with unknown statistics, and it estimates the row count from the function as 1 (or 100, depending on the SQL Server version). This makes cardinality estimation and join ordering guesswork.

-- Multi-statement TVF: BEGIN/END block, explicit table variable
CREATE FUNCTION dbo.GetCustomerSummary(@RegionID INT)
RETURNS @Result TABLE (
    CustomerID   INT,
    CustomerName NVARCHAR(200),
    TotalOrders  INT,
    TotalRevenue DECIMAL(18,2)
)
AS
BEGIN
    INSERT INTO @Result
    SELECT
        c.CustomerID,
        c.CustomerName,
        COUNT(o.OrderID),
        SUM(o.TotalAmount)
    FROM dbo.Customers c
    LEFT JOIN dbo.Orders o ON o.CustomerID = c.CustomerID
    WHERE c.RegionID = @RegionID
    GROUP BY c.CustomerID, c.CustomerName;

    RETURN;
END;
GO

-- View joining an mTVF
CREATE VIEW dbo.vw_RegionSummary AS
SELECT
    r.RegionID,
    r.RegionName,
    s.CustomerID,
    s.CustomerName,
    s.TotalOrders,
    s.TotalRevenue
FROM dbo.Regions r
CROSS APPLY dbo.GetCustomerSummary(r.RegionID) s;

The rough order from safest to most dangerous for large-result-set views: iTVF (optimizer can fold it in) > mTVF (opaque, poor estimates, but at least called once per join) > scalar UDF in SELECT list (called once per row, blocks parallelism).

The Scalar UDF Performance Trap (RBAR)

RBAR stands for Row By Agonizing Row. It is a term coined by SQL Server MVP Jeff Moden to describe any SQL pattern that forces the engine to process one row at a time instead of operating on a set. Scalar UDFs in a SELECT list are one of the canonical RBAR patterns.

Here is what happens at runtime: the view's SELECT list includes a call to dbo.GetFiscalYear(o.OrderDate). For every row the query produces, SQL Server must invoke the scalar function once — evaluate its T-SQL body, return a single value, move to the next row. On a table with 100 rows this is invisible. On a table with 500,000 rows you have 500,000 function invocations, each processed serially, each carrying the overhead of a T-SQL function call. The query stops being set-based and becomes a procedural loop wearing a SQL costume.

The symptoms show up clearly in a slow view query:

-- This looks innocent but is slow at scale
SELECT
    FiscalYear,
    SUM(TotalAmount) AS Revenue
FROM dbo.vw_OrdersWithFiscalYear   -- view calls dbo.GetFiscalYear per row
WHERE OrderDate >= '2025-01-01'
GROUP BY FiscalYear;

-- In the actual execution plan you will see:
--   "Compute Scalar" operator calling the UDF
--   Estimated rows: wildly wrong
--   No parallelism (scalar UDFs historically blocked parallel plans)
--   High CPU, low logical reads per second relative to what a set-based query would show

Beyond RBAR, scalar UDFs historically prevented the query optimizer from choosing a parallel execution plan for the entire query — not just the function call, but the whole thing. A 16-core server would execute the query on a single thread as if it had one. This was the default behavior through SQL Server 2017.

SQL Server 2019 Scalar UDF Inlining (Froid)

SQL Server 2019 introduced a feature called scalar UDF inlining (internal project name: Froid). When a scalar UDF qualifies, the optimizer rewrites it into an equivalent relational expression at parse time and folds it into the query tree. The function body essentially disappears as a separate execution unit, and the optimizer can apply indexes, parallel plans, and pushdown as normal. When it works, the RBAR penalty vanishes completely.

The catch is eligibility. Many real-world scalar UDFs do not qualify for inlining. Disqualifying conditions include: the function contains subqueries; the function calls another user-defined function (UDFs that call UDFs); the function uses certain built-ins like RAND(), NEWID(), or @@ROWCOUNT; the function modifies state (side effects); the function has complex control flow that Froid cannot transform. You can check whether inlining fired by reading the actual execution plan — look for the UDFInlining attribute on the plan node, or query sys.dm_exec_function_cache.

The practical rule for 2026: do not assume your scalar UDFs are safe because you are on SQL Server 2019+. Check the actual plan. If inlining did not fire, you still have RBAR.

Prefer Inline Table-Valued Functions

The standard fix for a scalar UDF in a view's SELECT list is to rewrite the logic as an inline table-valued function and join it in with CROSS APPLY. The optimizer can fold iTVFs into the outer query tree, preserving set-based execution, parallelism, and proper cardinality estimates.

Here is the before-and-after for the fiscal year example:

-- BEFORE: Scalar UDF in SELECT list (RBAR on large tables)
CREATE FUNCTION dbo.GetFiscalYear(@OrderDate DATE)
RETURNS INT
AS
BEGIN
    RETURN CASE
        WHEN MONTH(@OrderDate) >= 10 THEN YEAR(@OrderDate) + 1
        ELSE YEAR(@OrderDate)
    END;
END;
GO

CREATE VIEW dbo.vw_OrdersWithFiscalYear_Slow AS
SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalAmount,
    dbo.GetFiscalYear(o.OrderDate) AS FiscalYear  -- called once per row
FROM dbo.Orders o;

-- AFTER: Inline TVF with CROSS APPLY (optimizer folds it in)
CREATE FUNCTION dbo.GetFiscalYearInline(@OrderDate DATE)
RETURNS TABLE
AS
RETURN (
    SELECT
        CASE
            WHEN MONTH(@OrderDate) >= 10 THEN YEAR(@OrderDate) + 1
            ELSE YEAR(@OrderDate)
        END AS FiscalYear
);
GO

CREATE VIEW dbo.vw_OrdersWithFiscalYear_Fast AS
SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalAmount,
    fy.FiscalYear
FROM dbo.Orders o
CROSS APPLY dbo.GetFiscalYearInline(o.OrderDate) fy;

The result is functionally identical. The optimizer now sees the CASE expression directly in the plan — no black-box function call, no RBAR, no parallelism block. On a 500K-row Orders table, the difference is typically an order of magnitude in execution time.

If the logic you need to encapsulate is genuinely too complex for a single SELECT (multiple inserts, iterative processing, error handling), an mTVF is a better choice than a scalar UDF — at least the mTVF is called once per join row and not once per output column value. But if you find yourself reaching for an mTVF frequently, consider whether a staged approach (intermediate temp tables, CTEs, or a computed column) would serve you better. AI2SQL can suggest T-SQL rewrites for complex view patterns — paste the slow view and your schema, and it will flag the function-in-SELECT-list issues and propose iTVF alternatives.

Nesting Views and Functions: The Hidden Cost

The r/SQLServer post that sparked this guide was about the joy of discovery — and part of that joy is the natural next step: if functions in views are great, then views calling views calling functions must be even better, right? Each layer encapsulates the one below, and the top-level query stays beautifully simple.

The problem is that the optimizer's ability to see through layers degrades with each level. A view is transparent to the optimizer in the same way a subquery is — it can usually fold it in and optimize the full tree. But a view that calls a scalar UDF, inside a view that applies a filter, inside a view used by a report query, creates an optimization barrier at each UDF call. Predicates from the outer query cannot always be pushed down past the scalar function calls into the inner layers. Index seeks become range scans. Statistics that were accurate at the innermost layer become irrelevant after the function runs.

SARGability — the ability to use an index seek for a predicate — is particularly fragile here. If your outer query filters on WHERE FiscalYear = 2025 and FiscalYear is a column produced by a scalar UDF in an inner view, the optimizer cannot seek an index on FiscalYear because the column doesn't exist until the UDF runs. It must produce all rows, call the function on each one, and then filter.

-- Anti-pattern: SARGability lost through function wrapping
-- The outer filter cannot seek into the Orders table by FiscalYear
SELECT *
FROM dbo.vw_OrdersWithFiscalYear_Slow   -- scalar UDF computes FiscalYear
WHERE FiscalYear = 2025;                -- filter applied AFTER full scan + UDF calls

-- Better: filter before the function runs
SELECT
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    o.TotalAmount,
    fy.FiscalYear
FROM dbo.Orders o
CROSS APPLY dbo.GetFiscalYearInline(o.OrderDate) fy
WHERE o.OrderDate >= '2024-10-01'       -- seek-able: date range on an indexed column
  AND o.OrderDate  < '2025-10-01';

The deeper the view stack, the harder this is to reason about. A useful rule of thumb: if your view depends on another view that depends on a third view, check the actual execution plan on the top-level query. If you see table scans where you expect seeks, nested function calls are a likely cause.

When Functions-in-Views Are Actually Fine

The point of the r/SQLServer post wasn't wrong — functions in views are genuinely useful. The issues above apply specifically to scalar UDFs on large tables. There is a real set of cases where this pattern is safe and appropriate:

• Inline table-valued functions always: As shown above, iTVFs let the optimizer fold logic in. Use them freely. They are the correct tool for parameterized view-like patterns.
• Scalar UDFs on small result sets: If your view is always queried against a small lookup table (say, a few hundred rows of reference data), the per-row function overhead is negligible. The encapsulation benefit is real; the performance cost is invisible.
• Display/formatting columns on already-filtered output: If the scalar UDF computes a display-formatted string (currency symbol, human-readable status label) and the view is designed to be queried with a tight WHERE clause that reduces the result to tens or hundreds of rows before the formatting runs, the RBAR concern mostly disappears.
• SQL Server 2019+ with confirmed inlining: If you have checked the actual plan, confirmed the UDFInlining="1" attribute is present, and the query plan looks like an inlined expression rather than a per-row function call, you are fine. Verify after any function body change — a seemingly minor edit can disqualify the function from inlining.
• Computed columns persisted at write time: A variation that avoids the view/function overhead entirely: add a computed column to the base table with PERSISTED, store the function result at insert/update time, and build the view on top of the stored column. You pay the compute cost once per write, not on every read.

Debugging a Slow Function-Backed View

When a view that uses functions starts running slow, the diagnosis is straightforward if you know where to look. The actual execution plan (not the estimated plan) is your primary tool.

In SQL Server Management Studio: run your query with Include Actual Execution Plan enabled (Ctrl+M then execute). In Azure Data Studio: use the same option from the query toolbar. In the plan that appears:

• Look for a "Compute Scalar" operator that references your UDF. Hover over it — if it shows the scalar function name in the tooltip, this is where RBAR is happening. The estimated rows on this operator are typically 1, which tells you the optimizer gave up trying to model its output.
• Check for the absence of parallelism. The plan header shows "DegreeOfParallelism." If it is 1 on a query that should be able to use multiple threads, a scalar UDF (pre-2019 or non-inlining) may be the cause.
• Compare estimated vs actual rows at the table scan/seek level. A huge discrepancy (estimated 1, actual 500,000) often means the optimizer was blocked from reading statistics past a function call boundary.
• Verify UDF inlining on 2019+. Right-click the plan XML and search for UDFInlining. A value of 1 means inlining fired. A value of 0 means you still have the old behavior.

Once you have identified the slow function operator, the remediation path is usually one of: rewrite as an iTVF (preferred), refactor the scalar UDF to meet 2019 inlining eligibility, or move the computation to a persisted computed column.

If you are working through a rewrite and want a second opinion on the T-SQL, AI2SQL can take a slow view definition and suggest restructured versions — it understands T-SQL dialect specifics and can propose iTVF equivalents for common scalar UDF patterns. You still need to verify the rewrite with EXPLAIN on your real data, but it can accelerate the draft-and-iterate cycle. Start plan: $5/mo, 50 queries/day. Pro: $11/mo, 500/day. Team: $23/mo, unlimited.

Rewrite Your Slow Function-Backed Views

Frequently Asked Questions