Scalar Functions
A scalar user-defined function (UDF) takes zero or more parameters and returns a single value of a defined data type via RETURN, and can be used inline anywhere a scalar expression is valid — in a SELECT list, a WHERE clause, or a computed column. The catch is that, prior to SQL Server 2019's scalar UDF inlining feature, scalar functions referenced in a WHERE clause historically forced row-by-row execution instead of set-based processing, which could badly degrade performance on large tables.
Cricket analogy: A scalar function dbo.fn_StrikeRate(@runs, @balls) computing one number is like a scorer's mental formula applied to each batter individually — call it once per player and get back exactly one strike rate value.
Inline vs. Multi-Statement Table-Valued Functions
An inline table-valued function (iTVF) is essentially a parameterized view: its body is a single RETURN (SELECT ...) statement, and SQL Server expands it into the calling query's plan just like a view, allowing the optimizer to see through it and produce efficient set-based plans. A multi-statement table-valued function (MSTVF), by contrast, declares a table variable, populates it with one or more statements inside BEGIN...END, and returns that table — historically the optimizer treated its output as a fixed-size estimate, which frequently led to poor cardinality estimates and worse performance than an equivalent iTVF or view.
Cricket analogy: An inline TVF is like a pre-set field placement the umpire and captains both fully see and can adjust live, while a multi-statement TVF is like a substitute fielder brought on whose exact positioning the broadcast graphics can't predict in advance.
-- Inline table-valued function
CREATE OR ALTER FUNCTION dbo.fn_ActiveOrdersByCustomer (@CustomerId INT)
RETURNS TABLE
AS
RETURN
(
SELECT OrderId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
AND Status = 'Active'
);
GO
SELECT * FROM dbo.fn_ActiveOrdersByCustomer(101);
-- Scalar function
CREATE OR ALTER FUNCTION dbo.fn_StrikeRate (@Runs INT, @Balls INT)
RETURNS DECIMAL(6,2)
AS
BEGIN
IF @Balls = 0 RETURN 0;
RETURN CAST(@Runs AS DECIMAL(6,2)) / @Balls * 100;
END;Determinism and Restrictions
UDFs cannot perform side-effecting operations like INSERT, UPDATE, or DELETE against permanent tables, cannot call non-deterministic system functions such as GETDATE() inside a schema-bound function, and cannot use TRY...CATCH or dynamic SQL — restrictions that keep functions safe to call anywhere an expression is expected. Marking a function WITH SCHEMABINDING both prevents underlying tables/columns it depends on from being altered or dropped out from under it and is a prerequisite for creating an index on the results of a deterministic function used in a computed column.
Cricket analogy: A UDF's no-side-effects rule is like the third umpire reviewing a run-out — pure evaluation of evidence, no authority to actually change the scoreboard or send anyone off the field directly.
Multi-statement table-valued functions (MSTVFs) return a table variable whose row count the query optimizer estimated as a small fixed number in older compatibility levels, which can produce badly inefficient plans when the function actually returns many rows. Prefer inline table-valued functions or views when the logic permits, and reserve MSTVFs for cases that genuinely need procedural, multi-step construction of the result set.
- Scalar UDFs return a single typed value and can be used inline in SELECT, WHERE, or computed columns.
- Inline table-valued functions behave like parameterized views and are fully optimizable by the query engine.
- Multi-statement table-valued functions build a table variable procedurally but historically produce poor cardinality estimates.
- UDFs cannot perform INSERT/UPDATE/DELETE on permanent tables or use TRY...CATCH or dynamic SQL.
- WITH SCHEMABINDING locks dependent object definitions and is required for indexing computed columns based on the function.
- SQL Server 2019+ introduced scalar UDF inlining to automatically rewrite eligible scalar functions for set-based execution.
- Prefer iTVFs over MSTVFs whenever the logic can be expressed as a single SELECT statement.
Practice what you learned
1. What does an inline table-valued function's body consist of?
2. Why have multi-statement table-valued functions (MSTVFs) historically caused performance problems?
3. Which operation is a user-defined function NOT permitted to perform?
4. What is a key benefit of marking a function WITH SCHEMABINDING?
5. What feature did SQL Server 2019 introduce to improve scalar UDF performance?
Was this page helpful?
You May Also Like
Stored Procedures
Understand how to create, parameterize, and execute stored procedures to encapsulate reusable, precompiled T-SQL logic on the server.
Variables and Control Flow
Learn how T-SQL variables store scalar values inside a batch and how IF/ELSE and WHILE let you branch and loop through procedural logic.
Error Handling with TRY/CATCH
Learn how TRY...CATCH blocks intercept T-SQL runtime errors, how to inspect them with the ERROR_ functions, and how to combine them safely with transactions.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics