Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 39 ยท Procedures, Triggers and Functions

User-Defined Functions in SQL Server

Write SQL Server user-defined functions: scalar, inline table-valued and multi-statement functions. Learn APPLY, schema binding and why scalar UDFs can slow queries.

SQL Server ships with hundreds of built-in functions, but sooner or later you want your own: a tax calculation, a formatted name, a "all orders for this customer" lookup you use in ten queries. A user-defined function (UDF) packages such logic under a name and lets you call it from inside a query, which stored procedures can't do.

There are three kinds, and choosing the right one decides whether your query is fast or terrible. The functions can't run in the browser, so the T-SQL is read-only, each with a runnable stand-in made from views and expressions. For built-in functions see string functions and date functions.

Scalar functions

A scalar function returns one value and is used like any expression:

SQL
CREATE OR ALTER FUNCTION dbo.PriceWithTax (@Net decimal(10,2), @Rate decimal(5,4))
RETURNS decimal(10,2)
WITH SCHEMABINDING
AS
BEGIN
    RETURN ROUND(@Net * (1 + @Rate), 2);
END;
GO

SELECT Name, Price, dbo.PriceWithTax(Price, 0.19) AS Gross
FROM   dbo.Products;

Rules: always call with the schema name (dbo.PriceWithTax); a function can't change data in the database (no INSERT/UPDATE on permanent tables), can't use TRY...CATCH or most non-deterministic side effects, and can't run dynamic SQL.

The performance catch

Before SQL Server 2019, a scalar UDF ran once per row as an opaque call: the optimizer couldn't see into it, parallelism was disabled for the whole query, and SET STATISTICS didn't show the real cost. SQL Server 2019 can inline many simple scalar UDFs automatically (scalar UDF inlining), but not all (for example, ones using certain time functions or table variables). If a function is simple, consider an inline table-valued function or just writing the expression in the query.

The portable stand-in for a scalar function is a plain expression, or a view that holds it:

SQL A computed expression (stand-in for a scalar UDF)

Press Run (or Ctrl + Enter) to see the result here.

Inline table-valued functions (iTVF)

An inline table-valued function returns a table and consists of a single SELECT. Think of it as a parameterized view. The optimizer expands it into the calling query, so it is as fast as typing the query by hand:

SQL
CREATE OR ALTER FUNCTION dbo.OrdersForCustomer (@CustomerId int)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderId, OrderDate, Total
    FROM   dbo.Orders
    WHERE  CustomerId = @CustomerId
);
GO

SELECT * FROM dbo.OrdersForCustomer(42) WHERE Total > 100;

To call it once per row of another table, use CROSS APPLY or OUTER APPLY:

SQL
SELECT c.FullName, o.OrderId, o.Total
FROM   dbo.Customers AS c
CROSS APPLY dbo.OrdersForCustomer(c.CustomerId) AS o;

CROSS APPLY behaves like an inner join (customers with no orders disappear); OUTER APPLY keeps them with NULLs. APPLY is also the T-SQL way to get "top N per group":

SQL
SELECT c.FullName, t.OrderId, t.Total
FROM   dbo.Customers AS c
CROSS APPLY (SELECT TOP (2) OrderId, Total
             FROM dbo.Orders AS o
             WHERE o.CustomerId = c.CustomerId
             ORDER BY o.Total DESC) AS t;

The standards-based stand-in for "top 2 per customer" is a window function:

SQL Top 2 orders per customer

Press Run (or Ctrl + Enter) to see the result here.

A view with a parameter is the idea behind an iTVF. In SQLite, a view plus a WHERE clause plays that role:

SQL A view as a parameterless inline function

Press Run (or Ctrl + Enter) to see the result here.

Multi-statement table-valued functions

A multi-statement TVF declares a table variable, fills it with several statements and returns it:

SQL
CREATE OR ALTER FUNCTION dbo.TopProducts (@N int)
RETURNS @Result TABLE (ProductId int, Revenue money)
AS
BEGIN
    INSERT INTO @Result
    SELECT TOP (@N) ProductId, SUM(Quantity * UnitPrice)
    FROM dbo.OrderLines
    GROUP BY ProductId
    ORDER BY SUM(Quantity * UnitPrice) DESC;

    RETURN;
END;

They allow procedural logic, but the optimizer treats the result as a black box with a fixed row estimate (100 rows in older versions, the real count with interleaved execution in newer ones). If one SELECT is enough, prefer the inline form.

Comparing the three

Scalar Inline TVF Multi-statement TVF
Returns One value A table A table
Body BEGIN ... RETURN One SELECT Many statements
Optimizer sees inside Sometimes (2019+) Yes No
Typical use Small formatting or math Parameterized view, APPLY Complex logic

Good habits

  • Add WITH SCHEMABINDING so the objects a function uses can't be dropped under it; it is also required for indexed computed columns and helps determinism checks.
  • Don't use scalar UDFs in WHERE on big tables: they stop index seeks (the predicate is not sargable).
  • Don't put queries to big tables inside a scalar UDF that is called per row.
  • Built-ins first: STRING_AGG, TRIM, IIF, DATEDIFF, FORMAT (slow, use sparingly).
flowchart LR
  Q["Query"] --> S["Scalar UDF: one value per call"]
  Q --> I["Inline TVF: expanded into the query"]
  Q --> M["Multi-statement TVF: table variable, black box"]

Inline functions give the optimizer the most freedom.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which kind of function behaves like a parameterized view?

    An inline TVF is a single SELECT that the optimizer merges into the calling query.
  2. 2. What is the usual problem with scalar UDFs in older SQL Server versions?

    Before inlining in SQL Server 2019, each row caused a separate invocation and parallelism was disabled.
  3. 3. What does CROSS APPLY do compared with OUTER APPLY?

    CROSS APPLY is like an inner join; OUTER APPLY is like a left join.
  4. 4. Can a user-defined function modify permanent tables?

    SQL Server functions are side-effect free with respect to permanent tables; use a stored procedure for writes.

Summary

  • Scalar UDFs return one value, inline TVFs return a table from one SELECT, multi-statement TVFs build a table step by step.
  • Inline TVFs and APPLY are fast and flexible; scalar and multi-statement UDFs can hide costs.
  • Functions cannot change data; use WITH SCHEMABINDING and avoid UDFs in WHERE.

Next: Triggers in SQL Server