MSDN

Table-Valued Functions in SQL Server

Table-valued functions (TVFs) are a powerful feature in SQL Server that allow you to encapsulate complex query logic and return a result set that can be treated like a table. They offer advantages over stored procedures and scalar functions when you need to return multiple rows and columns.

Types of Table-Valued Functions

There are two main types of table-valued functions:

1. Inline Table-Valued Functions (ITVFs): These are simpler and generally perform better. They contain a single SELECT statement that returns the result set.
2. Multi-Statement Table-Valued Functions (MSTVFs): These allow for more complex logic, including multiple T-SQL statements, variable declarations, and control flow.

Inline Table-Valued Functions (ITVFs)

ITVFs are defined using a single SELECT statement. The syntax is as follows:

CREATE FUNCTION schema_name.function_name (@parameter1 datatype, @parameter2 datatype)
RETURNS TABLE
AS
RETURN
(
    SELECT column1, column2, ...
    FROM your_table
    WHERE some_condition = @parameter1
    -- Additional JOINs, WHERE clauses, etc.
);

Example of an Inline TVF:

This function returns all employees from a specified department.

CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, JobTitle
    FROM HumanResources.Employee
    WHERE DepartmentID = @DepartmentID
);

You can then query this function like a table:

SELECT *
FROM dbo.GetEmployeesByDepartment(5);

Multi-Statement Table-Valued Functions (MSTVFs)

MSTVFs allow for more complex logic involving multiple T-SQL statements. You declare a table variable to hold the results and then populate it.

CREATE FUNCTION schema_name.function_name (@parameter1 datatype)
RETURNS @ReturnTable TABLE (
    column1 datatype,
    column2 datatype,
    ...
)
AS
BEGIN
    -- Declare local variables if needed

    -- Populate the @ReturnTable table variable with data
    INSERT INTO @ReturnTable (column1, column2, ...)
    SELECT columnA, columnB, ...
    FROM another_table
    WHERE some_condition = @parameter1;

    -- You can perform more operations here:
    -- UPDATE @ReturnTable SET column1 = UPPER(column1);

    RETURN;
END;

Example of a Multi-Statement TVF:

This function returns a list of customers who have placed orders in a given year and calculates the total amount spent by each.

CREATE FUNCTION dbo.GetCustomerSpendingByYear (@OrderYear INT)
RETURNS @CustomerSpending TABLE (
    CustomerID INT,
    CustomerName NVARCHAR(200),
    TotalAmount DECIMAL(18, 2)
)
AS
BEGIN
    INSERT INTO @CustomerSpending (CustomerID, CustomerName, TotalAmount)
    SELECT
        c.CustomerID,
        c.CompanyName,
        SUM(od.Quantity * od.UnitPrice * (1 - od.Discount)) AS Total
    FROM Sales.Customer AS c
    JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
    JOIN Sales.SalesOrderDetail AS od ON soh.SalesOrderID = od.SalesOrderID
    WHERE YEAR(soh.OrderDate) = @OrderYear
    GROUP BY c.CustomerID, c.CompanyName;

    RETURN;
END;

Querying the MSTVF:

SELECT *
FROM dbo.GetCustomerSpendingByYear(2014)
ORDER BY TotalAmount DESC;

Advantages of Table-Valued Functions

• Reusability: Encapsulate complex logic to be used in multiple queries.
• Readability: Make your queries cleaner and easier to understand.
• Modularity: Break down complex data retrieval into smaller, manageable units.
• Performance: Can sometimes improve performance by reducing redundant code and allowing for better query plan optimization (especially ITVFs).

Considerations

• TVFs do not have output parameters.
• TVFs cannot perform DML operations that modify data in permanent tables (though they can modify table variables within their scope).
• For operations that require extensive data modification, stored procedures might be a better choice.