Scalar vs Inline TVF vs Multi-Statement TVF: Choosing the Right SQL Server Function Type

Three function types, three very different performance profiles — now that you’ve built one of each, here’s the decision made simple, plus the stored-procedure exit ramp that applies whenever none of the three actually fit.

Side by Side

	Scalar	Inline TVF	Multi-statement TVF
Returns	One value	A table	A table
Optimizer visibility	Limited (improved 2019+)	Full — inlines like a view	None — black box, fixed row estimate
Procedural logic	Yes	No, single SELECT only	Yes
Usable inside a JOIN	N/A (scalar value)	Yes, joins like a table	Yes, joins like a table
Default preference	3rd choice	1st choice	Last resort — consider a procedure first

The Decision Flow

The Real Decision Isn’t Always Among These Three

The most important line in the flowchart is the last branch: “mTVF or Procedure.” If you need to modify data (INSERT/UPDATE/DELETE), manage a transaction, or use TRY/CATCH error handling — none of which any function type can do — that’s your unambiguous signal to write a stored procedure instead, which is exactly where the next chapter picks up. Functions are for computing and returning values; procedures are for doing things, including things that change data.

-- FAILS: functions cannot modify data outside a local table variable
CREATE FUNCTION dbo.BadIdea (@id INT) RETURNS INT AS
BEGIN
    UPDATE dbo.Employee SET last_login = GETDATE() WHERE employee_id = @id; -- not allowed
    RETURN 1;
END;
-- Msg 443: Invalid use of a side-effecting operator 'UPDATE' within a function.

This restriction isn’t arbitrary — it’s what allows functions to be safely called from inside a SELECT list or WHERE clause at all. If functions could silently modify data, using one inside a SELECT would make the query’s meaning depend on evaluation order, which SQL’s set-based model deliberately doesn’t guarantee.

Key Takeaways

Default to iTVF whenever a single SELECT expresses the logic — it’s the best-performing option, with zero downside versus writing the JOIN by hand
Scalar functions are fine for reusable expressions on modest data volumes, called outside a large table’s WHERE clause
mTVFs and stored procedures both handle procedural logic — if you need to modify data or manage transactions, that’s your firm signal to reach for a procedure instead, since no function type permits it

Practice tip: Take one real reporting need from your own work or a hobby project, and walk it through this decision flow explicitly before writing any code — “can one SELECT do it? do I need one value or a table? am I modifying data?” Making this a conscious three-question checklist, rather than an instinct, is what actually sticks.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

Scalar Functions in SQL Server: Reusable Logic With a Real Performance Cost

Chapter 4 of the Fundamentals course previewed that user-defined functions exist. This is that promise kept — starting with the simplest kind. A scalar function takes parameters and returns a single value, usable anywhere an expression is valid, exactly like a built-in function such as DATEDIFF. Simple to write, but genuinely not free at scale, which is the entire reason this chapter exists before stored procedures.

Writing One

CREATE FUNCTION dbo.CalculateAge (@birthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @birthDate, GETDATE())
         - CASE WHEN (MONTH(@birthDate) > MONTH(GETDATE()))
                  OR (MONTH(@birthDate) = MONTH(GETDATE()) AND DAY(@birthDate) > DAY(GETDATE()))
                THEN 1 ELSE 0 END;
END;
GO

SELECT first_name, dbo.CalculateAge(birth_date) AS age FROM dbo.Employee;

Notice this fixes the exact DATEDIFF boundary-crossing gotcha from the Fundamentals course (Chapter 4) — a naive DATEDIFF(YEAR, birth_date, GETDATE()) overcounts by one until the birthday actually passes this year. Wrapping the correct logic in a function means every caller gets it right automatically, instead of everyone re-deriving (and possibly getting wrong) the same CASE expression.

The Performance Trap

Scalar UDFs referenced in a WHERE clause or SELECT list against every row of a large table historically prevent SQL Server from parallelizing the query and get called once per row — the query optimizer has no visibility into what’s happening inside the function body, so it can’t reason about it the way it reasons about ordinary SQL. SQL Server 2019+ improved this significantly with “scalar UDF inlining,” which can automatically rewrite qualifying simple functions into inline expressions — but it’s still something to profile, not assume is free, since not every function qualifies (ones using TRY/CATCH, temp tables, or certain other constructs opt out of inlining).

-- Seeing the difference directly: compare actual execution plans
SET STATISTICS TIME ON;
SELECT COUNT(*) FROM dbo.Employee WHERE dbo.CalculateAge(birth_date) > 30;
SELECT COUNT(*) FROM dbo.Employee WHERE DATEDIFF(YEAR, birth_date, GETDATE()) > 30; -- inline equivalent
SET STATISTICS TIME OFF;

Common mistake: Assuming a scalar function is “basically the same speed” as inline logic because it looks similarly simple in the CREATE FUNCTION body. On a table with a few hundred rows, you likely won’t notice a difference. On a table with millions of rows, a row-by-row function call in the WHERE clause can be the single biggest bottleneck in an otherwise well-indexed query — and it won’t show up as an obvious “missing index” warning, since the problem isn’t the index, it’s the function call itself.

When Scalar Functions Are Genuinely the Right Call

Despite the caveat above, scalar functions are still a good tool for reusable logic that’s called on modest data volumes, in a SELECT list rather than a WHERE clause on a huge table, or inside a stored procedure operating on a handful of rows at a time. A formatting function for phone numbers, a business-rule lookup used in a report generated nightly — these are fine. The concern scales specifically with row count and where in the query the function appears, not with the mere existence of a scalar function in your codebase.

DETERMINISTIC vs Not: A Subtle Constraint

-- This function CANNOT be used in a computed persisted column or an index,
-- because GETDATE() makes it non-deterministic (a different result each call):
CREATE FUNCTION dbo.DaysUntilToday (@date DATE) RETURNS INT
AS BEGIN RETURN DATEDIFF(DAY, @date, GETDATE()); END;

A function is deterministic only if it always returns the same output for the same input, with no dependency on the current date/time, random values, or external state. This matters beyond just style — SQL Server won’t let you use a non-deterministic function in a persisted computed column or as part of an index key, because the stored value would go stale the moment time passes.

Practice tip: Write dbo.CalculateAge yourself from the example above, then run it against a table with a few thousand synthetic rows (a numbers-table trick like the ones used in later capstones works well) in a WHERE clause, timing it with SET STATISTICS TIME ON. Compare against the inline DATEDIFF equivalent. Seeing an actual millisecond difference on your own machine is far more convincing than any explanation of why it happens.

Enjoyed this?

Subscribe to get every new SQL Server lesson as soon as it’s published, and share it with a developer who’d find it useful.

📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook

Want the full structured course with quizzes, projects, and 10+ exercises per chapter? Check out SQL Server for Developers & DBAs, coming soon on this site.

Tag: User-Defined Functions