Home > SQL Server > Reference > Functions > Table-Valued Functions

Table-Valued Functions (TVFs)

Table-Valued Functions (TVFs) are a type of Transact-SQL (T-SQL) function that returns a table. They offer a powerful way to encapsulate complex logic, improve code reusability, and enhance query performance. TVFs can be used in place of tables in many T-SQL statements, such as the FROM clause of a query, or as arguments to other functions.

There are two main types of Table-Valued Functions:

Inline Table-Valued Functions (ITVFs)

Inline Table-Valued Functions are simpler and often more performant than Multi-Statement TVFs. They are defined by a single SELECT statement.

Syntax


CREATE FUNCTION schema_name.function_name
( [ @parameter [ datatype ] [ = default ] [ OUT | OUTPUT ] ]
    [ ,...n ]
)
RETURNS TABLE
[ WITH <function_option> [<function_option>,...n] ]
AS
RETURN
(
    SELECT select_list
    FROM table_sources
    [ WHERE search_conditions ]
    [ GROUP BY ... ]
    [ HAVING ... ]
    [ ORDER BY ... ]
)
[;]<function_option> ::=
    | SCHEMABINDING
    | <return_table_type_definition>
        <return_table_type_definition> ::=
            TABLE ( column_name data_type [ column_constraint ] [ ,...n ] )
        | CHECK | ENCRYPTION | EXECUTE AS | INSTEAD OF | ROWCOUNT | TRANSPARENTDATAENCRYPTION

Example

Get Employee Details by Department

This function returns a table of employees belonging to a specified department.


CREATE FUNCTION dbo.GetEmployeesByDepartment (@DepartmentName NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT
        E.EmployeeID,
        E.FirstName,
        E.LastName,
        D.DepartmentName
    FROM
        Employees AS E
    JOIN
        Departments AS D ON E.DepartmentID = D.DepartmentID
    WHERE
        D.DepartmentName = @DepartmentName
);
GO

-- Usage:
SELECT * FROM dbo.GetEmployeesByDepartment('Sales');

Multi-Statement Table-Valued Functions (MSTVFs)

Multi-Statement Table-Valued Functions allow for more complex logic, including multiple T-SQL statements, variable declarations, and conditional logic. The final result is populated into a table variable that is then returned.

Syntax


CREATE FUNCTION schema_name.function_name
( [ @parameter [ datatype ] [ = default ] [ OUT | OUTPUT ] ]
    [ ,...n ]
)
RETURNS @return_variable TABLE
    [ <return_table_type_definition> ]
    [ WITH <function_option> [<function_option>,...n] ]
AS
BEGIN
    -- T-SQL statements to populate @return_variable
    RETURN
END
[;]<function_option> ::=
    | SCHEMABINDING
    | <return_table_type_definition>
        <return_table_type_definition> ::=
            TABLE ( column_name data_type [ column_constraint ] [ ,...n ] )
        | CHECK | ENCRYPTION | EXECUTE AS | INSTEAD OF | ROWCOUNT | TRANSPARENTDATAENCRYPTION

Example

Calculate Employee Salary with Bonus

This function calculates the total salary for employees, including a potential bonus based on performance.


CREATE FUNCTION dbo.CalculateEmployeeTotalSalary (@EmployeeID INT)
RETURNS @EmployeeSalaries TABLE
(
    EmployeeID INT PRIMARY KEY,
    BaseSalary DECIMAL(10, 2),
    Bonus DECIMAL(10, 2),
    TotalSalary DECIMAL(10, 2)
)
AS
BEGIN
    DECLARE @BaseSalary DECIMAL(10, 2);
    DECLARE @Bonus DECIMAL(10, 2) = 0;

    SELECT @BaseSalary = Salary
    FROM Employees
    WHERE EmployeeID = @EmployeeID;

    -- Simulate bonus calculation based on performance
    IF EXISTS (SELECT 1 FROM PerformanceReviews WHERE EmployeeID = @EmployeeID AND Rating > 4)
    BEGIN
        SET @Bonus = @BaseSalary * 0.10; -- 10% bonus
    END

    INSERT INTO @EmployeeSalaries (EmployeeID, BaseSalary, Bonus, TotalSalary)
    VALUES (@EmployeeID, @BaseSalary, @Bonus, @BaseSalary + @Bonus);

    RETURN;
END
GO

-- Usage:
SELECT * FROM dbo.CalculateEmployeeTotalSalary(101);

Key Differences and Considerations

Feature Inline TVF (ITVF) Multi-Statement TVF (MSTVF)
Definition Single SELECT statement Multiple T-SQL statements, including variable declarations and control flow
Performance Generally better optimization by the query optimizer, as it can be expanded in-line Can sometimes be less performant as the optimizer treats them more like black boxes. Results are materialized into a table variable first.
Complexity Simpler logic Supports more complex business logic and procedural operations
Use Cases Simple data retrieval, filtering, and transformations Complex calculations, data aggregation across multiple steps, and procedural data manipulation
SCHEMABINDING Supported Not supported
Performance Tip: Whenever possible, prefer Inline TVFs for their potential performance benefits. Only use Multi-Statement TVFs when the complexity of the logic necessitates it.

When to Use Table-Valued Functions