What is a Table-Valued Function in SQL Server?

In SQL Server, a table-valued function (TVF) is a user-defined function that returns a table. This is in contrast to a scalar function, which returns a single value.

You can invoke a table-valued function in the same way that you can query a table. For example, you can use it in a SELECT statement. In some cases, table-valued functions can also be used to update, delete, and insert data.

Types of Table-Valued Functions

When you create a table-valued function, you have the choice of creating a Transact-SQL table-valued function or a Common Language Runtime (CLR) table-valued function.

Transact-SQL Table-Valued Functions

Transact-SQL TVFs can be one of the following:

Inline Table-Valued Function (ITVF): When you create an ITVF, you start the function’s definition with RETURNS TABLE, and the subsequent SELECT statement defines the structure of the return table.
Multi-Statement Table-Valued Function (MSTVF): A multi-statement table-valued function can contain multiple statements, the results of which are saved to a variable that you declare at the start of the function. When you do this, you explicitly specify the structure of the return table.

CLR Table-Valued Functions

From the CLR perspective, the syntax is similar to the T-SQL ITVF, but slightly different. You explicitly specify the structure of the return table, but you don’t declare a return variable.

CLR table-valued functions are implemented as methods on a class in a Microsoft .NET Framework assembly.

For a more detailed overview of CLR TVFs, see Microsoft’s documentation for CLR Table-Valued Functions.

Example 1 – Inline Table-Valued Function

Here’s an example of the T-SQL code used to create an inline table-valued function.

CREATE FUNCTION dbo.udf_CatsByName_ITVF( @CatName varchar(70) )
    RETURNS TABLE
AS
RETURN (
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName
    );
GO

We can tell that this is an inline table-valued function, because it doesn’t specify the structure of the return table. It simply states RETURNS TABLE, then relies on the SELECT statement to determine the return table’s structure.

In this case, the function requires the cat’s name to be passed in as an argument.

See Create an Inline Table-Valued Function for examples of adding options such as schema binding and encryption.

Schema binding is usually a good idea, as it will prevent any adverse changes being done to the underlying objects that the function references.

Example 2 – Multi-Statement Table-Valued Function

Here’s how we’d write the function if we wanted it to be a multi-statement table-valued function.

CREATE FUNCTION udf_CatsByName_MSTVF( @CatName varchar(70) )
    RETURNS @cats TABLE (
        CatId int,
        CatName varchar(70),
        Phone varchar(10)
    )
AS
BEGIN
    INSERT INTO @cats
    SELECT 
        CatId,
        CatName,
        Phone
    FROM dbo.Cats
    WHERE CatName = @CatName;

    RETURN;
END;
GO

In this case, we use a variable of type table called @cats and we explicitly specify the structure of the return table. The query results are stored in that variable, which is then returned when the function is invoked.

This example doesn’t do MSTVFs much justice, because I only include a single statement. The main point of MSTVFs is that you can include multiple statements, and you can add the output of those statements to the return variable.

See Create a Multi-Statement Table-Valued Function for an example of using multiple statements, as well examples of adding options such as schema binding and encryption.

Example 3 – Select Data from our Table-Valued Functions

So now that we’ve created our functions, we can invoke them both using a SELECT statement.

SELECT * FROM udf_CatsByName_ITVF('Tom');
SELECT * FROM udf_CatsByName_MSTVF('Tom');

Result:

+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 3       | Tom       | 7871237652 |
+---------+-----------+------------+
(1 row affected)
+---------+-----------+------------+
| CatId   | CatName   | Phone      |
|---------+-----------+------------|
| 3       | Tom       | 7871237652 |
+---------+-----------+------------+
(1 row affected)

Both the ITVF and the MSTVF were invoked using the same syntax and both returned the same result.

Invoking Table-Valued Functions

Table-valued functions can be invoked where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements.

This means you can select data, insert data, update data, and even delete data via a table-valued function.

Here are articles that demonstrate each one: