Creación de funciones con valores de tabla
Las funciones con valores de tabla permiten encapsular la lógica de consulta compleja en componentes reutilizables que devuelven conjuntos de resultados. Puede llamar a estas funciones directamente en consultas, al igual que las tablas o vistas, lo que hace que el código sea más modular y fácil de mantener.
Al compilar aplicaciones de base de datos, a menudo es necesario recuperar conjuntos de datos filtrados o calculados basados en parámetros de entrada. Las funciones con valores de tabla resuelven este problema mediante el empaquetado de la lógica de consulta en funciones que aceptan parámetros y devuelven tablas. A diferencia de los procedimientos almacenados, puede usar funciones con valores de tabla en cláusulas JOIN e instrucciones SELECT, lo que proporciona flexibilidad para tratar los resultados de la función como orígenes de datos.
Descripción de los tipos de función con valores de tabla
SQL Server proporciona dos tipos de funciones con valores de tabla, cada una adecuada para distintos escenarios.
Función insertada con valores de tabla
Contiene una sola SELECT instrucción y devuelve resultados directamente. Con las funciones en línea, no defines la estructura de la tabla: SQL Server la deduce de tu SELECT declaración. El optimizador de consultas trata funciones insertadas con valores de tabla como vistas con parámetros, lo que suele producir mejores planes de ejecución.
Función con valores de tabla de varias instrucciones
Usa un BEGIN...END bloque y declara explícitamente la estructura de la tabla devuelta. Este tipo proporciona más control cuando necesita ejecutar varias instrucciones, realizar cálculos complejos o compilar el conjunto de resultados de forma iterativa. Sin embargo, esta flexibilidad incluye un equilibrio de rendimiento, ya que el optimizador trata estas funciones de forma diferente.
La elección entre estas funciones depende de sus requisitos específicos. Para consultas sencillas con parámetros, las funciones insertadas proporcionan un mejor rendimiento. Cuando necesite lógica procedimental o varios pasos para construir el conjunto de resultados, las funciones multideclaración serán necesarias.
Crear funciones de tabla con valores en línea
Las funciones insertadas con valores de tabla ofrecen una manera concisa de parametrizar consultas. Los define con una única instrucción RETURN seguida de una consulta SELECT.
En el ejemplo siguiente se muestra una función insertada que recupera pedidos para un cliente específico:
CREATE FUNCTION dbo.GetCustomerOrders
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
TotalAmount,
Status
FROM Sales.Orders
WHERE CustomerID = @CustomerID
);
Ahora puede usar esta función en consultas como una tabla:
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';
La función acepta el parámetro id. de cliente y devuelve solo los pedidos del cliente. Puede filtrar aún más, JOINo agregar los resultados según sea necesario. Este enfoque mantiene limpia la consulta principal al encapsular la lógica de filtrado del cliente.
Creación de funciones con valores de tabla de varias instrucciones
Las funciones con valores de tabla de varias instrucciones proporcionan más flexibilidad cuando necesita realizar varias operaciones para compilar el conjunto de resultados.
Considere una función que calcula los resúmenes de ventas de productos con varias agregaciones:
CREATE FUNCTION dbo.GetProductSalesSummary
(
@StartDate DATE,
@EndDate DATE
)
RETURNS @SalesSummary TABLE
(
ProductID INT,
ProductName NVARCHAR(100),
TotalQuantity INT,
TotalRevenue DECIMAL(18,2),
AveragePrice DECIMAL(18,2)
)
AS
BEGIN
INSERT INTO @SalesSummary
SELECT
p.ProductID,
p.ProductName,
SUM(od.Quantity) AS TotalQuantity,
SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
AVG(od.UnitPrice) AS AveragePrice
FROM Production.Products p
INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY p.ProductID, p.ProductName;
RETURN;
END;
Observe cómo declarar explícitamente la variable @SalesSummary de tabla con columnas y tipos de datos específicos. El cuerpo de la función inserta datos en esta variable de tabla y, a continuación, lo devuelve. Esta estructura permite agregar lógica de procesamiento adicional, control de errores o instrucciones condicionales según sea necesario.
Uso de funciones con valores de tabla en consultas
Las funciones con valores de tabla se integran sin problemas en las consultas, lo que permite patrones eficaces de recuperación de datos.
Puede combinar los resultados de la función con otras tablas:
SELECT
c.CustomerName,
s.ProductName,
s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;
El CROSS APPLY operador llama a la función para cada fila de la tabla Customers, aunque en este ejemplo, los parámetros de función son constantes. Cuando se pasan valores de columna como parámetros, CROSS APPLY resulta especialmente útil:
SELECT
c.CustomerName,
o.OrderID,
o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';
Esta consulta recupera todos los pedidos completados para cada cliente, lo que muestra cómo las funciones con valores de tabla habilitan el procesamiento de filas por fila dentro de las consultas. La función actúa como una subconsulta correlacionada, pero con mejor legibilidad y reutilización.
En el caso de las funciones con valores de tabla insertadas que no requieren evaluación de fila por fila, también puede usar la sintaxis INNER JOIN:
SELECT
c.CustomerName,
o.OrderDate,
o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;
Con estas técnicas, puede crear consultas complejas a partir de componentes de función más sencillos y probados, lo que mejora la capacidad de mantenimiento del código y la eficacia del desarrollo.