LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 3219 min read

User-Defined Functions

Learn the difference between scalar and table-valued functions in SQL Server, how to create them with CREATE FUNCTION, and how to call them in a query.

Introduction

A user-defined function (UDF) packages a calculation or a reusable result set behind a name that you can call directly inside a query, the same way you would call a built-in function like UPPER() or GETDATE(). SQL Server supports two broad kinds: scalar functions, which return a single value, and table-valued functions, which return a result set you can query like a table. This lesson covers both, with runnable examples of each.

What You Will Learn
  • The difference between scalar and table-valued functions.
  • How to create and call a scalar function.
  • How to create and query an inline table-valued function.
  • How multi-statement table-valued functions differ.
  • When to reach for a function instead of a procedure.

Scalar vs Table-Valued

TypeReturnsUsed Where
Scalar functionA single value (INT, VARCHAR, DATE, etc.)In a SELECT list, WHERE clause, or anywhere an expression is valid
Inline table-valued function (iTVF)A table, defined by a single SELECTIn a FROM clause, like a parameterized view
Multi-statement table-valued function (MSTVF)A table, built up with multiple statementsIn a FROM clause, for logic too complex for one SELECT

Creating a Scalar Function

A scalar function is defined with CREATE FUNCTION, a RETURNS clause naming the data type it produces, and a body that computes and returns that value.

CREATE FUNCTION dbo.fn_GetFullName
(
    @FirstName VARCHAR(50),
    @LastName  VARCHAR(50)
)
RETURNS VARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;
GO

Calling a Scalar Function

A scalar function must be called with its schema prefix (dbo.) and can be used anywhere an expression is allowed, including a SELECT list.

SELECT
    CustomerID,
    dbo.fn_GetFullName(FirstName, LastName) AS FullName
FROM dbo.Customers
WHERE City = 'Seattle';
Result

Click Run to see what this code prints.

Scalar Functions and Performance

Scalar functions that query tables internally are historically a common performance trap in SQL Server, because (on older compatibility levels) they run row-by-row instead of set-based. Recent versions include 'scalar UDF inlining' that can help, but it is still worth preferring simple, computation-only scalar functions and using table-valued functions or views for anything that touches other tables.

Inline Table-Valued Functions

An inline table-valued function behaves like a parameterized view: its body is a single RETURN (SELECT ...) statement, and SQL Server expands it into the calling query, which usually makes it fast.

CREATE FUNCTION dbo.fn_GetOrdersByCustomer
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate, Status
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
);
GO


SELECT * FROM dbo.fn_GetOrdersByCustomer(104)
ORDER BY OrderDate DESC;
Result

Click Run to see what this code prints.

Multi-Statement Table-Valued Functions

When the logic needs more than one statement - for example, populating a table variable step by step - use a multi-statement table-valued function. It explicitly declares the shape of the returned table with RETURNS @TableVar TABLE (...).

CREATE FUNCTION dbo.fn_GetTopCustomers
(
    @MinOrders INT
)
RETURNS @Result TABLE
(
    CustomerID INT,
    OrderCount INT
)
AS
BEGIN
    INSERT INTO @Result (CustomerID, OrderCount)
    SELECT CustomerID, COUNT(*)
    FROM dbo.Orders
    GROUP BY CustomerID
    HAVING COUNT(*) >= @MinOrders;


    RETURN;
END;
GO


SELECT * FROM dbo.fn_GetTopCustomers(3);
Result

Click Run to see what this code prints.

Functions vs Procedures

Functions and procedures overlap in purpose but are not interchangeable. A function can be used inside a larger SELECT statement, but it cannot modify data or use TRY/CATCH, and it always has to return something. A procedure can perform inserts, updates, and deletes, use transactions and error handling, but cannot be called directly inside a SELECT list or FROM clause the way a function can.

Common Mistakes

Avoid These Mistakes
  • Writing a scalar function that queries a large table and calling it once per row in a SELECT - this can be very slow.
  • Forgetting the dbo. schema prefix when calling a function.
  • Trying to use a function to INSERT/UPDATE/DELETE data - functions cannot modify data outside table variables.
  • Choosing a multi-statement table-valued function when a simpler inline table-valued function would do, losing optimizer flexibility.
  • Not indexing the columns a table-valued function filters on, just as you would for any other query.

Best Practices

  • Prefer inline table-valued functions over multi-statement ones when the logic fits in a single SELECT.
  • Keep scalar functions to pure computation where possible - avoid querying tables inside them if you can use a join or an iTVF instead.
  • Name functions with a clear fn_ prefix so they are easy to distinguish from tables and views.
  • Test functions with a range of inputs, including NULLs and boundary values.
  • Document the expected shape of the returned table for table-valued functions.

Frequently Asked Questions

No, functions cannot execute stored procedures or run dynamic SQL - they are restricted to read-only, deterministic-friendly operations so SQL Server can safely use them inside larger queries.

Unlike built-in functions, user-defined scalar and table-valued functions always require an explicit schema prefix when called - it is a SQL Server parsing rule, not a style choice.

Inline table-valued functions perform similarly to views since both get expanded into the outer query, but functions have the added benefit of accepting parameters, which plain views cannot.

No, functions can only return their declared return value; if you need OUTPUT parameters, use a stored procedure instead.

Key Takeaways

  • Scalar functions return one value; table-valued functions return a table.
  • Inline table-valued functions act like parameterized views and are usually the most efficient option.
  • Multi-statement table-valued functions support more complex logic at some performance cost.
  • Functions cannot modify data outside of table variables and cannot call procedures.
  • Always call user-defined functions with their schema prefix, such as dbo.fn_GetFullName.

Summary

User-defined functions let you package reusable calculations and parameterized result sets directly into your T-SQL vocabulary. Next, you will look at triggers - code that runs automatically in response to data changes, rather than being called explicitly.

Next Lesson →

Triggers