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.