Scalar/Table-Valued Functions, Inline TVFs, UDF Inlining, and Performance Tradeoffs

Learning outcomes

ServiceHub developers want reusable expressions and reusable rowsets. One developer wraps a calculation in a scalar user-defined function (UDF); another turns a filter into an inline table-valued function (iTVF); a third builds a multi-statement table-valued function (MSTVF) because it “looks like a procedure that returns a table.” The three objects can expose similarly convenient syntax while presenting very different information to the optimizer.

The central design question is optimizer visibility. Can SQL Server see and transform the relational logic inside the function when optimizing the outer query, or is the function an opaque/partially opaque boundary with its own execution and cardinality behavior?

1. Functions return values; they are not miniature procedures

A T-SQL function must return a scalar value or a table value and is restricted from performing arbitrary database-state changes. That makes functions composable inside queries, but it also means they should represent calculations or rowset expressions rather than hidden commands. A procedure can return multiple result sets and perform general DML; a function cannot be used as a drop-in replacement for that behavior.

USE master;GOIF DB_ID(N'ServiceHubProgrammabilityLab') IS NULL  CREATE DATABASE ServiceHubProgrammabilityLab;GOALTER DATABASE ServiceHubProgrammabilityLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubProgrammabilityLab;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'api') IS NULL EXEC(N'CREATE SCHEMA api AUTHORIZATION dbo;');IF SCHEMA_ID(N'lab13') IS NULL EXEC(N'CREATE SCHEMA lab13 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.WorkOrder',N'U') IS NULLBEGIN  CREATE TABLE ops.WorkOrder  (    work_order_id bigint IDENTITY(1001,1) NOT NULL      CONSTRAINT PK_ops_WorkOrder PRIMARY KEY,    customer_code varchar(16) NOT NULL,    region_code char(3) NOT NULL,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    description nvarchar(400) NULL,    CONSTRAINT CK_ops_WorkOrder_status      CHECK (status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')),    CONSTRAINT CK_ops_WorkOrder_priority CHECK (priority BETWEEN 1 AND 5)  );  ;WITH n AS  (    SELECT TOP (30000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT ops.WorkOrder(customer_code,region_code,status,priority,opened_at,closed_at,amount,description)  SELECT CONCAT('CUST-',RIGHT('000000'+CONVERT(varchar(6),n%9000),6)),         CASE n%3 WHEN 0 THEN 'N01' WHEN 1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n%23=0 THEN 'ESCALATED'              WHEN n%5=0 THEN 'CLOSED'              WHEN n%2=0 THEN 'ASSIGNED' ELSE 'OPEN' END,         CONVERT(tinyint,1+n%5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n%5=0 THEN DATEADD(minute,n+60,'2026-01-01T00:00:00') END,         CAST(25+(n%25000)/10.0 AS decimal(12,2)),         CONCAT(N'ServiceHub work order ',n)  FROM n;  CREATE INDEX IX_ops_WorkOrder_region_status_opened    ON ops.WorkOrder(region_code,status,opened_at)    INCLUDE(customer_code,priority,amount,closed_at);END;GO

USE ServiceHubProgrammabilityLab;GOCREATE OR ALTER FUNCTION api.PriorityBand(@priority tinyint)RETURNS varchar(8)WITH SCHEMABINDING, INLINE=ONASBEGIN  RETURN CASE WHEN @priority>=4 THEN 'HIGH'              WHEN @priority>=2 THEN 'NORMAL' ELSE 'LOW' END;END;GOCREATE OR ALTER FUNCTION api.OpenOrdersByRegion(@region_code char(3))RETURNS TABLEWITH SCHEMABINDINGASRETURN(  SELECT work_order_id,customer_code,region_code,priority,opened_at,amount  FROM ops.WorkOrder  WHERE region_code=@region_code AND status='OPEN');GOCREATE OR ALTER FUNCTION api.OpenOrdersByRegion_Multi(@region_code char(3))RETURNS @r TABLE(  work_order_id bigint PRIMARY KEY,  customer_code varchar(16),priority tinyint,opened_at datetime2(0),amount decimal(12,2))ASBEGIN  INSERT @r(work_order_id,customer_code,priority,opened_at,amount)  SELECT work_order_id,customer_code,priority,opened_at,amount  FROM ops.WorkOrder  WHERE region_code=@region_code AND status='OPEN';  RETURN;END;GO

The iTVF is essentially a parameterized relational expression: the optimizer can normally substitute its single query into the calling query and optimize the combined tree. The MSTVF populates a table variable internally. That materialization boundary means the outer optimizer does not have ordinary column statistics on the returned table. Modern interleaved execution can improve eligible plans by executing the MSTVF during optimization to obtain actual cardinality, but that does not make every MSTVF equivalent to an iTVF.

2. Scalar UDF inlining changed an old performance rule—but did not erase it

Historically, scalar T-SQL UDFs often caused iterative row-by-row invocation, under-costed work, isolated statement optimization, and serial query plans. Starting with SQL Server 2019 and database compatibility level 150, scalar UDF inlining can transform eligible UDF logic into scalar expressions or relational subqueries inside the calling query. Then the optimizer can cost and transform that logic with the rest of the plan.

SELECT o.name,o.type_desc,m.is_inlineable,m.inline_typeFROM sys.objects AS oJOIN sys.sql_modules AS m ON m.object_id=o.object_idWHERE o.object_id IN (OBJECT_ID(N'api.PriorityBand'),OBJECT_ID(N'api.OpenOrdersByRegion'),  OBJECT_ID(N'api.OpenOrdersByRegion_Multi'));GOSELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name=N'TSQL_SCALAR_UDF_INLINING';GO

is_inlineable=1 means the module definition has constructs that permit inlining; Microsoft explicitly warns that it does not prove every calling query will inline it. Calling context, query shape, UDF constructs, signatures, side-effecting/time-dependent functions, compatibility and hints can prevent the transformation.

SET STATISTICS IO ON;SET STATISTICS TIME ON;GOSELECT TOP (10000)       work_order_id,priority,api.PriorityBand(priority) AS priority_bandFROM ops.WorkOrderORDER BY work_order_id;GOSELECT TOP (10000)       work_order_id,priority,api.PriorityBand(priority) AS priority_bandFROM ops.WorkOrderORDER BY work_order_idOPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));GOSET STATISTICS IO OFF;SET STATISTICS TIME OFF;

Capture an actual plan locally and inspect whether a UserDefinedFunction node remains. Do not copy timing numbers from this course: dataset size, CPU, cache state and build change the result. The reproducible lesson is structural—successful inlining removes the opaque iterative UDF operator and exposes the logic to optimization.

3. Inline TVF vs MSTVF: same FROM syntax, different planning evidence

SELECT r.work_order_id,r.customer_code,r.amountFROM api.OpenOrdersByRegion('N01') AS rWHERE r.priority>=4ORDER BY r.opened_at DESC;GOSELECT r.work_order_id,r.customer_code,r.amountFROM api.OpenOrdersByRegion_Multi('N01') AS rWHERE r.priority>=4ORDER BY r.opened_at DESC;GO

For an iTVF the optimizer sees the base-table predicates and can push filters, reorder joins and use base statistics. For an MSTVF, older plans used fixed heuristics (100 rows since SQL Server 2014; one row in earlier versions). SQL Server 2017+ can use interleaved execution for eligible MSTVF references, pausing optimization to materialize the function and use its actual row count before finalizing the outer plan. Eligibility, compatibility and query shape matter, so verify the plan rather than repeating either old heuristic as universal truth.

4. Determinism and SCHEMABINDING are design properties, not magic speed switches

SCHEMABINDING prevents referenced schema objects from being changed incompatibly while the function depends on them, and deterministic functions need schema binding for several indexability scenarios. It does not automatically make a function fast. Likewise, a deterministic function always produces the same result for a given relevant state/input; that property matters for indexed expressions and correctness, not as a general performance guarantee.

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,o.name,o.type_desc,       OBJECTPROPERTYEX(o.object_id,'IsDeterministic') AS is_deterministic,       m.is_schema_bound,m.is_inlineableFROM sys.objects AS oJOIN sys.sql_modules AS m ON m.object_id=o.object_idWHERE o.object_id IN (OBJECT_ID(N'api.PriorityBand'),OBJECT_ID(N'api.OpenOrdersByRegion'));GOSELECT referencing_id,referenced_schema_name,referenced_entity_nameFROM sys.sql_expression_dependenciesWHERE referencing_id=OBJECT_ID(N'api.OpenOrdersByRegion');GO

5. Failure cases that look convenient in code review

Three patterns deserve suspicion. First, a scalar UDF that performs a lookup for every outer row can become a row-by-row bottleneck when it cannot inline. Second, a large MSTVF can create a poor initial estimate or workspace cost when interleaved execution is not applicable. Third, hiding filtering or conversion logic in functions can make SARGability and cardinality harder to reason about. Reuse is valuable only when it does not conceal the data-access mechanism from the team.

CREATE OR ALTER FUNCTION lab13.UtcAgeMinutes(@opened_at datetime2(0))RETURNS intASBEGIN  -- Time-dependent intrinsic functions block scalar-UDF inlining eligibility.  RETURN DATEDIFF(minute,@opened_at,SYSUTCDATETIME());END;GOSELECT o.name,m.is_inlineableFROM sys.objects AS oJOIN sys.sql_modules AS m ON m.object_id=o.object_idWHERE o.object_id=OBJECT_ID(N'lab13.UtcAgeMinutes');GODROP FUNCTION lab13.UtcAgeMinutes;GO

The point is not “never call SYSUTCDATETIME.” It is to understand that semantics can intentionally prevent inlining. If a time value should be consistent for one operation, an application/procedure can capture the timestamp once and pass it into a pure function or query expression.

Production judgment

Prefer iTVFs for parameterized rowsets that are naturally one relational expression. Use scalar functions for genuinely reusable calculations, then verify whether inlining happens under representative calling queries. Use MSTVFs when their imperative shape is justified, and validate interleaved execution/cardinality/grants instead of relying on version folklore. Treat function schema binding and determinism as correctness/indexability properties, not tuning buttons.

The lab needs SQL Server 2025 Developer or Express, compatibility 170, and ordinary CREATE FUNCTION/ALTER permissions on the lab schemas. Scalar UDF inlining requires compatibility 150+ and remains subject to documented eligibility rules. No server restart or paid feature is required.

Authoritative references

• Scalar UDF inlining — eligibility, compatibility and plan evidence
• User-defined functions — function types and restrictions
• Create user-defined functions — iTVF/MSTVF examples and optimizer caveats
• CREATE FUNCTION — function syntax and options
• SQL Server 2025 build versions — servicing baseline