System vs User-Defined Functions in SQL Server: A First Look

Every function you’ve used so far — COUNT, GETDATE, UPPER — ships with SQL Server itself. Soon (in the advanced course) you’ll write your own. Here’s the map before you get there, including why the choice between function types is a genuine engineering decision, not just a syntax preference.

Two Categories

A Preview of What a Scalar UDF Looks Like

You’re not expected to write this yet, but seeing the shape demystifies it — it’s really just a named, reusable calculation:

CREATE FUNCTION dbo.CalculateAge (@birthdate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @birthdate, GETDATE());
END;

-- Once created, it's used exactly like a built-in function:
SELECT name, dbo.CalculateAge('1995-06-15') AS age FROM dbo.Startup;

Notice it slots directly into a SELECT list, just like UPPER() or DATEDIFF() — from the caller’s perspective, a well-written scalar UDF is indistinguishable from a system function. That’s exactly the point: it extends SQL Server’s function library with your own domain-specific logic.

Why the Choice Between These Types Actually Matters

This isn’t just a naming exercise. A scalar UDF called inside a WHERE clause against every row of a large table can quietly turn a query that should take milliseconds into one that takes seconds — because (in most SQL Server versions before 2019’s scalar UDF inlining improvements) the function executes once per row, outside the query optimizer’s usual cost-based reasoning. An inline TVF, by contrast, gets expanded directly into the surrounding query and optimized like ordinary SQL. This single distinction — can the optimizer see through it or not — is one of the most consequential real-world performance decisions a T-SQL developer makes, and it’s covered in full in the advanced course once you have the query-plan-reading skills to actually verify the difference yourself.

Where This Goes Next

Writing your own functions — and understanding the real performance tradeoffs between the three types — is a full topic in SQL Server for Developers & DBAs (Chapter 2 there is dedicated entirely to this). For now, the goal is simply recognizing that this whole other category exists, and that “which function type” is a real design decision, not an arbitrary label.