Inline Table-Valued Functions: Parameterized Views That Stay Fast

Reusable query logic can stay visible inside the caller's execution plan. Inline table-valued functions let the optimizer see a single SELECT inside the calling statement. That visibility makes them useful for parameterized query logic.

Start With the Required Customer Result

Suppose a report needs each customer's latest order date. A scalar function is a natural first expression because it returns one value. The important question is how SQL Server executes the complete customer report, not whether the function name looks convenient.

I define missing-order behavior before rewriting the function. A customer without orders should remain in this report with a NULL latest date. A function that returns no row can interact differently with CROSS APPLY. Preserve that contract before comparing execution plans.

Use a disposable database for the sample. The customer and order identifiers are artificial inputs. The index starts with CustomerID and then OrderDate, supporting the correlated aggregate. It does not prove that every application should create the same index. Evaluate existing indexes and write cost before applying the pattern elsewhere.

CREATE TABLE dbo.FunctionCustomersDemo(CustomerID int PRIMARY KEY);
CREATE TABLE dbo.FunctionOrdersDemo
(OrderID int PRIMARY KEY, CustomerID int NOT NULL, OrderDate date NOT NULL);
INSERT dbo.FunctionCustomersDemo VALUES(1),(2),(3);
INSERT dbo.FunctionOrdersDemo VALUES
(11,1,'20260901'),(12,1,'20260915'),(21,2,'20260910');
CREATE INDEX IX_FunctionOrdersDemo_CustomerDate
ON dbo.FunctionOrdersDemo(CustomerID,OrderDate);
GO

Establish the Scalar Baseline

The scalar function assigns the aggregate to a variable and returns it. Call it from the customer query while collecting the actual plan and STATISTICS TIME. Record the returned customer rows too. A faster expression that drops customers has failed the comparison.

CREATE FUNCTION dbo.LastOrderScalarDemo(@CustomerID int)
RETURNS date
AS
BEGIN
 DECLARE @LastDate date;
 SELECT @LastDate=MAX(OrderDate)
 FROM dbo.FunctionOrdersDemo WHERE CustomerID=@CustomerID;
 RETURN @LastDate;
END;
GO
SET STATISTICS TIME ON;
SELECT CustomerID,dbo.LastOrderScalarDemo(CustomerID) AS LastOrderDate
FROM dbo.FunctionCustomersDemo ORDER BY CustomerID;
SET STATISTICS TIME OFF;
GO

Older scalar-function execution can hide per-row work from the main relational plan. Current SQL Server versions can inline eligible scalar functions. SQL Server 2019 introduced that optimization under the required compatibility and configuration conditions. Eligibility is not a guarantee that a particular call was inlined.

Therefore, do not claim every scalar function always runs row by row. Inspect the actual plan and current configuration. The is_inlineable column in sys.sql_modules shows whether a scalar function qualifies, and the sample function reported 1 on SQL Server 2025. A scalar baseline already inlined by the optimizer can perform similarly to an explicit relational rewrite. That is a useful result, not a failed demonstration.

Inline Table-Valued Functions Return One Expression

An inline table-valued function returns the result of one SELECT. It has no procedural body with DECLARE statements and multiple assignments. The returned column names and types come from the expression. An aggregate without GROUP BY produces one row, including when no input orders match.

CREATE FUNCTION dbo.LastOrderInlineDemo(@CustomerID int)
RETURNS TABLE
AS
RETURN
(
 SELECT MAX(OrderDate) AS LastOrderDate
 FROM dbo.FunctionOrdersDemo WHERE CustomerID=@CustomerID
);
GO
SET STATISTICS TIME ON;
SELECT c.CustomerID,f.LastOrderDate
FROM dbo.FunctionCustomersDemo c
CROSS APPLY dbo.LastOrderInlineDemo(c.CustomerID) f
ORDER BY c.CustomerID;
SET STATISTICS TIME OFF;
GO

That aggregate shape preserves the customer without orders and returns NULL. If the function instead returns matching detail rows, CROSS APPLY removes customers with no match. OUTER APPLY would preserve them. Choose APPLY semantics from the required result rather than copying the operator name from another example.

See Inline Table-Valued Functions in the Combined Plan

The inline expression can be expanded into the calling query. Look for the orders access, aggregate, joins, and estimates in the combined plan. The optimizer can then consider transformations across the function boundary instead of treating the function as an opaque procedure.

Do not expect one operator layout on every dataset. A small customer set and a broad customer report can favor different strategies. The index's selectivity and the chosen projection also affect the result. Compare estimates with actual rows before interpreting the plan's apparent simplicity.

I compare the whole report rather than timing one function invocation. The cost belongs to all requested customers and all underlying work. Which operator changes when the function becomes visible, and does that change reduce reads or CPU for the real workload?

Express Conditional Values With CASE

An inline function cannot run procedural IF branches. CASE can choose a value inside the SELECT when the condition belongs to expression logic. The following function returns both the last date and a readable ordering-status label.

CREATE FUNCTION dbo.OrderStatusInlineDemo(@CustomerID int)
RETURNS TABLE
AS
RETURN
(
 SELECT MAX(OrderDate) AS LastOrderDate,
        CASE WHEN COUNT_BIG(*)=0 THEN 'No orders'
             ELSE 'Has orders' END AS OrderStatus
 FROM dbo.FunctionOrdersDemo WHERE CustomerID=@CustomerID
);
GO
SELECT c.CustomerID,f.LastOrderDate,f.OrderStatus
FROM dbo.FunctionCustomersDemo c
CROSS APPLY dbo.OrderStatusInlineDemo(c.CustomerID) f;
GO

CASE chooses scalar results, not separate procedural query plans under your control. Both branches still need compatible result types. Do not use it to disguise side effects or assume it short-circuits every possible expression evaluation. Keep the returned values simple and test their boundary behavior.

Logic Inline Table-Valued Functions Cannot Hold

An inline function cannot declare local working tables, execute several statements, or perform data modifications. A single SELECT can still contain joins, subqueries, aggregates, and CASE expressions. That is substantial relational logic, but it does not replace every procedural workflow.

A multistatement table-valued function has a different implementation and optimization behavior. Current versions can improve estimates through eligible intelligent query processing features, but the function body remains a different model. Do not describe it as interchangeable with the inline RETURN SELECT form.

If the original function has several dependent steps, first examine whether one relational expression preserves its meaning. Otherwise choose another appropriate module rather than forcing the logic into an unreadable expression. Readability and correct results still matter after a plan boundary disappears.

Test Nulls, Duplicates, and Call Volume

Add customers with no orders, one order, several orders, and repeated order dates. Verify that the maximum date remains correct in every case. If the requirement later asks for the latest order identifier too, MAX(OrderDate) alone cannot identify that row. Introduce a stable tie-breaking rule.

Use representative customer counts when comparing performance. Tiny samples explain semantics but cannot establish production timing. Include compile time, execution time, and reads where appropriate. Repeat under comparable cache conditions without disrupting a shared server.

A useful reusable expression also needs stable naming and an explicit schema. Review permissions under the actual application login. The caller's ability to execute a convenient function should be tested alongside the complete query, not assumed from an administrator's successful demonstration.

Keep the Rewrite Evidence

Preserve the original definition and captured plan before deployment. Retain both query results for an equivalence review. Compare the current scalar optimization behavior with the proposed inline expression so the claimed benefit reflects the actual engine, not an outdated general rule.

Then choose the simpler maintainable design that meets the result and performance requirements. A function is a packaging decision as well as an execution choice. The strongest argument for the rewrite is visible relational logic plus verified behavior, with any performance gain measured on the workload that matters.

Inline table-valued functions expose relational logic while preserving a reusable interface. Compare complete caller behavior before replacing existing modules with inline table-valued functions.

Related reading on this blog: Avoid Functions in the WHERE Clause for Performance and Functions and Missing Indexes: SQL in Sixty Seconds 204.

An inline function is not a separate procedural work queue, it is one relational expression the optimizer can combine with its caller.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.