Funciones definidas por el usuario: reutilizar un cálculo o un conjunto

Algunas reglas aparecen una y otra vez dentro de consultas: calcular un importe, obtener una etiqueta o seleccionar un conjunto mediante un parámetro. Una función les asigna un nombre y permite utilizarlas como parte de otra consulta.

En SQL Server son comunes las funciones escalares, que devuelven un valor, y las funciones con valor de tabla, que devuelven filas.

Función escalar

Una función escalar devuelve un solo valor por cada invocación. En el cálculo siguiente, ROUND(expresión, 2) redondea el importe a dos posiciones decimales antes de devolverlo.

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

Se invoca con el esquema y el nombre de la función:

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

La función hace visible una regla compartida, pero no debemos asumir que envolver una expresión siempre mejora el rendimiento. Medimos su costo cuando se evalúa sobre muchas filas.

Función con valor de tabla en línea

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

La función se utiliza en FROM como una tabla parametrizada:

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

Una función con valor de tabla en línea define su resultado con una sola consulta. Suele integrarse mejor en el plan de ejecución que una función con varias instrucciones y una variable de tabla interna. El optimizador, el componente que compara estrategias de ejecución, puede considerar la consulta consumidora junto con esa definición.

Diferencia frente a una vista

Una vista expone una consulta sin parámetros. Una función con valor de tabla acepta valores y devuelve un conjunto que puede unirse, filtrarse o agruparse.

Si necesitamos varios conjuntos de resultados, modificaciones, control de transacciones o una operación con efectos más amplios, un procedimiento almacenado suele representar mejor la intención.

Límites importantes

Las funciones deben comportarse como expresiones reutilizables. Un efecto secundario es un cambio fuera del valor devuelto, como modificar una tabla. Las funciones no pueden realizar libremente esos efectos, devolver varios conjuntos independientes ni controlar transacciones como un procedimiento.

También conviene evitar esconder consultas costosas detrás de un nombre sencillo. Que la llamada ocupe una línea no significa que su ejecución sea barata.

Qué conviene comprobar

Revisamos tipos de entrada y salida, tratamiento de NULL, precisión decimal, determinismo y costo dentro del plan. Una función es determinista cuando las mismas entradas y el mismo estado producen el mismo resultado. Una función escalar puede ejecutarse conceptualmente por cada fila; versiones recientes pueden optimizar ciertos casos, pero no todas cumplen las condiciones necesarias.

Cuando la regla es breve y solo se utiliza una vez, una expresión directa puede ser más clara. Creamos una función cuando el nombre, la reutilización o el contrato justifican el objeto adicional.

Buscar en SQL Server