User-defined functions: reuse a calculation or a set

Some rules appear over and over again within queries: calculate an amount, get a label, or select a set using a parameter. A function gives them a name and allows them to be used as part of another query.

Scalar functions, which return a value, and table-valued functions, which return rows, are common in SQL Server.

Scalar function

A scalar function returns a single value for each invocation. In the following calculation, ROUND(expresión, 2) rounds the amount to two decimal places before returning it.

CREATE OR ALTER FUNCTION dbo.ufn_ImportePartida
(
    @Cantidad INT,
    @PrecioUnitario DECIMAL(12, 2),
    @Descuento DECIMAL(5, 2)
)
RETURNS DECIMAL(14, 2)
AS
BEGIN
    RETURN ROUND(
        @Cantidad * @PrecioUnitario * (1 - @Descuento / 100.0),
        2
    );
END;
GO

It is invoked with the schema and the function name:

SELECT
    d.IdDetallePedido,
    dbo.ufn_ImportePartida(
        d.Cantidad,
        d.PrecioUnitario,
        d.Descuento
    ) AS Importe
FROM dbo.DetallePedido AS d;

The function makes a shared rule visible, but we should not assume that wrapping an expression always improves performance. We measure its cost when it is evaluated over many rows.

Inline table-valued function

CREATE OR ALTER FUNCTION dbo.ufn_ProductosPorCategoria
(
    @IdCategoria SMALLINT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        p.IdProducto,
        p.Nombre,
        p.Precio,
        p.Existencia
    FROM dbo.Producto AS p
    WHERE p.IdCategoria = @IdCategoria
      AND p.Activo = 1
);
GO

The function is used in FROM as a parameterized table:

SELECT
    p.IdProducto,
    p.Nombre,
    p.Precio
FROM dbo.ufn_ProductosPorCategoria(2) AS p
WHERE p.Existencia > 0
ORDER BY p.Nombre;

An inline table-valued function defines its result with one query. It usually integrates into the execution plan better than a function with several statements and an internal table variable. The optimizer, the component that compares execution strategies, can consider the consuming query together with that definition.

Difference vs. a view

A view exposes a query without parameters. A table-valued function accepts values and returns an array that can be joined, filtered, or grouped.

If we need multiple result sets, modifications, transaction control, or an operation with broader effects, a stored procedure often better represents the intent.

Important limits

Functions should behave like reusable expressions. A side effect is a change outside the returned value, such as modifying a table. Functions cannot freely perform those effects, return several independent sets, or control transactions like a procedure.

It is also a good idea to avoid hiding expensive queries behind a simple name. Just because the call occupies a line does not mean that its execution is cheap.

What should be checked

We review input and output types, treatment of NULL, decimal precision, determinism, and cost in the plan. A function is deterministic when the same inputs and state produce the same result. A scalar function may conceptually run for each row; recent versions can optimize some cases, but not every function meets the required conditions.

When the rule is brief and only used once, a direct expression may be clearer. We create a feature when the name, reuse, or contract justifies the additional object.