User-Defined Functions in SQL Server

User-defined functions package reusable logic you can call inside queries — in SELECT lists, WHERE clauses, and joins. SQL Server has three kinds, and they are not interchangeable: one of them (the multi-statement kind) is a notorious performance trap, while another (inline table-valued) is effectively a parameterized view and optimizes beautifully. This tutorial builds each kind against real data and explains when each belongs.

Scalar functions — one value in, one value out

CREATE FUNCTION dbo.GetCustomerTotal (@CustomerId INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN (SELECT ISNULL(SUM(Amount), 0)
            FROM Orders WHERE CustomerId = @CustomerId);
END;

SELECT Name, dbo.GetCustomerTotal(CustomerId) AS LifetimeValue
FROM Customers
ORDER BY LifetimeValue DESC;

Name   LifetimeValue
-----  -------------
Rahul  2300.00
Meera  2090.00
Asha   1650.00
John   0.00
Sara   0.00

Readable — and historically dangerous: a scalar UDF that touches tables executes once per row, invisibly. Five customers, five subqueries; five million customers, five million. Modern SQL Server (2019+) can inline many scalar UDFs into the calling query, which largely fixes this — but inlining has a long list of disqualifiers (GETDATE(), recursion, table variables…). Check whether yours inlined: SELECT name, is_inlineable FROM sys.sql_modules JOIN sys.objects .... The safe habits remain: keep scalar UDFs for genuinely scalar computation (formatting, math), and express data lookups as joins or the next kind.

Inline table-valued functions — parameterized views

One RETURN (SELECT …), no BEGIN/END, returns a table:

CREATE FUNCTION dbo.OrdersAbove (@MinAmount DECIMAL(10,2))
RETURNS TABLE
AS RETURN (
    SELECT o.OrderId, c.Name, o.Amount
    FROM Orders o
    JOIN Customers c ON c.CustomerId = o.CustomerId
    WHERE o.Amount >= @MinAmount
);

SELECT * FROM dbo.OrdersAbove(900) ORDER BY Amount DESC;

OrderId  Name   Amount
-------  -----  -------
3        Rahul  2300.00
1        Asha   1200.00
6        Meera   990.00

This is the good table function: the optimizer expands it into the outer query like a view, indexes apply, estimates stay accurate. Compose it like any table — join it, filter it further, feed it outer-row values with CROSS APPLY:

SELECT c.Name, f.Amount
FROM Customers c
CROSS APPLY dbo.OrdersAbove(500) f
WHERE f.Name = c.Name;

If a requirement can be written as an inline TVF, write it as one.

Multi-statement table-valued functions — the trap

The same signature but with a declared table variable filled by multiple statements:

CREATE FUNCTION dbo.CustomerSummary ()
RETURNS @Result TABLE (Name NVARCHAR(50), OrderCount INT, Total DECIMAL(10,2))
AS
BEGIN
    INSERT INTO @Result
    SELECT c.Name, COUNT(o.OrderId), ISNULL(SUM(o.Amount), 0)
    FROM Customers c LEFT JOIN Orders o ON o.CustomerId = c.CustomerId
    GROUP BY c.Name;

    UPDATE @Result SET Total = 0 WHERE OrderCount = 0;   -- extra logic "needs" MSTVF
    RETURN;
END;

It works — but the optimizer treats the result as a black box with a tiny fixed row estimate (100 rows in older versions), so joining an MSTVF against real tables regularly produces catastrophic plans. Almost every MSTVF can be rewritten as an inline TVF (with CASE/CTE absorbing the "extra logic") or a stored procedure filling a temp table. Treat MSTVFs as a last resort and keep their results small.

Rules all functions share

• No side effects — no INSERT/UPDATE/DELETE on real tables, no calling stored procedures. Functions must be safe to run any number of times during a query.
• Deterministic functions (same inputs → same output) can back computed/indexed columns; nondeterministic ones (GETDATE()) can't be indexed.
• Errors inside functions can't be TRY/CATCHed inside the function — handle them in the caller (TRY/CATCH).

Need side effects, transactions, or output parameters? That's a stored procedure — the full comparison is in stored procedure vs user-defined function.

Managing functions

CREATE OR ALTER FUNCTION dbo.GetCustomerTotal ...   -- idempotent deploys
DROP FUNCTION dbo.GetCustomerTotal;

-- everything defined:
SELECT name, type_desc FROM sys.objects
WHERE type IN ('FN','IF','TF');    -- scalar, inline TVF, multi-statement TVF

Choosing in one table

Default to inline TVFs for anything set-shaped, keep scalar functions computation-only, and let stored procedures do the writing.