Table-Valued Functions (TVFs)

Reusable query objects that return data as a table. Use them to encapsulate complex logic and simplify queries.

Inline Table-Valued Functions

Defined with a single SELECT statement. The optimizer treats them like a view.

CREATE FUNCTION dbo.GetOrdersByCustomer(@CustomerID INT) RETURNS TABLE AS RETURN SELECT OrderID, OrderDate, TotalAmount FROM dbo.Orders WHERE CustomerID = @CustomerID;

Usage:

SELECT * FROM dbo.GetOrdersByCustomer(42);

Multi-Statement Table-Valued Functions

Contain multiple statements; return result via a table variable.

CREATE FUNCTION dbo.GetTopCustomers(@TopN INT) RETURNS @Result TABLE (CustomerID INT, TotalSpent MONEY) AS BEGIN INSERT INTO @Result SELECT TOP(@TopN) CustomerID, SUM(TotalAmount) AS TotalSpent FROM dbo.Orders GROUP BY CustomerID ORDER BY TotalSpent DESC; RETURN; END;

Usage:

SELECT * FROM dbo.GetTopCustomers(5);

Best Practices

Prefer inline TVFs for better performance.
Avoid excessive complexity inside TVFs.
Document input parameters clearly.
Use schema binding where appropriate.