Difference Between Multi-Statement Table-Valued Functions & Inline Table-Valued Functions in SQL Server

When you create a table-valued function (TVF) in SQL Server, you can either make it an inline table-valued function (ITVF) or a multi-statement table-valued function (MSTVF). There are differences between these function types, and they use a different syntax accordingly.

This article covers the difference between MSTVFs and ITVFs.

The Differences

Here are the main differences between MSTVFs and ITVFs.

	ITVF	MSTVF
The RETURNS Syntax	You simply state RETURNS TABLE and the return table’s definition will be based on the function’s SELECT statement. No need to specify the structure of the return table.	Your RETURNS syntax explicitly specifies the structure of the return table. This is done by declaring a TABLE variable that will be used to store and accumulate the rows that are returned as the value of the function.
The BEGIN/END Syntax	ITVFs do not use the BEGIN/END syntax.	MSTVFs do use the BEGIN/END syntax.
Performance	Generally faster than MTSVFs.	Generally slower than ITVFs.
Data Updates	In some cases it’s possible to update data in the underlying tables using an ITFV.	You cannot update data in the underlying tables using a MSTVF.

Syntax

Let’s look at the differences in the syntax of each function type.

Inline Table-Valued Function

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] [ READONLY ] }   
    [ ,...n ]  
  ]  
)  
RETURNS TABLE  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    RETURN [ ( ] select_stmt [ ) ]  
[ ; ]  

Multi-Statement Table-Valued Function

CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type   
    [ = default ] [READONLY] }   
    [ ,...n ]  
  ]  
)  
RETURNS @return_variable TABLE <table_type_definition>  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN  
    END  
[ ; ]

Notice that the MSTVF starts with a table definition, but the ITVF has no such definition.

The MSTVF starts with RETURNS @return_variable TABLE followed by the table definition. Here, @return_variable is a TABLE variable, used to store and accumulate the rows that should be returned as the value of the function.

Example 1 – Inline Table-Valued Function

Here’s an example of a simple ITVF.

CREATE FUNCTION udf_PetsByName_ITVF( @PetName varchar(70))
    RETURNS TABLE 
AS
RETURN (
    SELECT 
        CONCAT('Cat', ' ', CatId) AS PetId,
        CatName
    FROM dbo.Cats
    WHERE CatName = @PetName

    UNION ALL

    SELECT 
        CONCAT('Dog', ' ', DogId) AS PetId,
        DogName
    FROM dbo.Dogs
    WHERE DogName = @PetName
    );

GO

Here, I select from two tables using UNION ALL, and the function simply returns the result.

Example 2 – Multi-Statement Table-Valued Function

Here’s an example of using an MSTVF to do the same thing, but in a different way.

CREATE FUNCTION udf_PetsByName_MSTVF( @PetName varchar(70))
    RETURNS @pets TABLE (
        PetId varchar(20),
        PetName varchar(70)
    )
AS
BEGIN
    INSERT INTO @pets
    SELECT 
        CONCAT('Cat', ' ', CatId),
        CatName
    FROM dbo.Cats
    WHERE CatName = @PetName;

    INSERT INTO @pets
    SELECT 
        CONCAT('Dog', ' ', DogId),
        DogName
    FROM dbo.Dogs
    WHERE DogName = @PetName;

    RETURN;
END;

GO

The function starts with declaring a TABLE variable called @pets. In doing this, we explicitly specify the structure of the return table.

The queries inside the BEGIN/END block are saved to the TABLE variable called @pets.

In this case, I chose not to use UNION ALL. Instead, I executed the statements separately and saved the results of each one to the @pets variable.

Example 3 – Add Another Statement to the MSTVF

To further demonstrate the “multi-statement” aspect of MSTVFs, we can add more statements to the above MSTVF and save the results to the same return variable.

Example:

CREATE FUNCTION udf_PetsByName_MSTVF( @PetName varchar(70))
    RETURNS @pets TABLE (
        PetId varchar(20),
        PetName varchar(70)
    )
AS
BEGIN
    INSERT INTO @pets
    SELECT 
        CONCAT('Cat', ' ', CatId),
        CatName
    FROM dbo.Cats
    WHERE CatName = @PetName;

    INSERT INTO @pets
    SELECT 
        CONCAT('Dog', ' ', DogId),
        DogName
    FROM dbo.Dogs
    WHERE DogName = @PetName;

    IF @@ROWCOUNT = 0
    BEGIN
        INSERT INTO @pets
        VALUES (
            '',
            'There are no pets of that name.'
            )
    END

    RETURN;
END;

GO

In this case I added some code to return a special message whenever the query results in no rows being returned.