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.