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;

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.