In SQL Server, the inline table-valued function is one of two types of Transact-SQL table-valued functions (the other type being the multi-statement table-valued function).
Table-valued functions (TVF) are a type of user-defined function that return their results as a table. They can therefore be queried just like a normal table.
Inline TVFs (sometimes referred to as ITVFs) do not have associated return variables. The return value is defined through a single SELECT
statement. This statement defines the structure of the return table. This is in contrast to multi-statement TVFs (also referred to as MSTVFs), which do require a return variable.
ITVFs also do not use the BEGIN
/END
syntax, which is another thing that distinguishes them from MSTVFs.
Inline TVFs are often considered to have better performance over multi-statement TVFs, although this will also depend on what you’re trying to do in the function.
Example of an Inline Table-Valued Function
Here’s an example of a basic ITVF:
CREATE FUNCTION dbo.ufn_AlbumsByArtist(@ArtistId int) RETURNS TABLE AS RETURN ( SELECT al.ArtistId, al.AlbumName, g.Genre FROM dbo.Albums al INNER JOIN dbo.Artists ar ON al.ArtistId = ar.ArtistId INNER JOIN Genres g ON al.GenreId = g.GenreId WHERE al.ArtistId = @ArtistId ); GO
It basically consists of a SELECT
statement wrapped inside some other code. To specifically make it an inline TVF, I started the function with RETURNS TABLE
, followed closely by RETURN
, and ending with a SELECT
statement inside parentheses.
Multiple Statements
Although inline table-valued functions are not designed for multiple SELECT
statements (that’s what MSTVFs are for), it is possible to use the UNION
operator to combine the result set of multiple statements.
Example:
CREATE FUNCTION [dbo].[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
Function Options
You can also specify things like, whether or not to use schema binding (you probably should), and whether or not to encrypt the function.
Schema binding will prevent any adverse changes being done to the underlying objects that the function depends on (such as dropping a table, altering a column, etc).
Encryption will convert the function’s definition to an obfuscated format (to prevent others from being able to read it).
See Create an Inline Table-Valued Function for examples of adding schema binding and encryption to an ITVF.