You can create an inline table-valued function (ITVF) in SQL Server using the T-SQL CREATE FUNCTION syntax.

Syntax

Here’s the official syntax for inline TVFs.

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 [ ) ]  
[ ; ]  

Example 1 – Basic ITVF

Here’s an example of a basic 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

In this case, the function requires a cat name be passed in as an argument. It then uses this argument in the query in order to return the relevant data.

Example 2 – Add Schema Binding

It’s usually a good idea to schema bind your functions by using the SCHEMABINDING argument.

Doing this will ensure that the underlying tables can’t be changed in a way that would affect your function.

Without schema binding, the underlying tables could be modified or even deleted. Doing this could break the function.

Here’s the same function, but this time with schema binding:

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

GO

Notice I used the two-part name when referencing the table in my query (I used dbo.Cats when referencing the table, instead of just Cats). Doing this is a requirement for schema binding an object. If you try to schema bind an object without using two-part names you’ll get an error.

Now that I’ve schema bound my function, if I try to drop the table referenced in its definition, I get an error:

DROP TABLE Cats;

Result:

Msg 3729, Level 16, State 1, Line 1
Cannot DROP TABLE 'cats' because it is being referenced by object 'udf_CatsByName_ITVF'.

By the way, here’s what happens if I try to create the function without using two-part naming:

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

GO

Result:

Msg 4512, Level 16, State 3, Procedure udf_CatsByName_ITVF, Line 7
Cannot schema bind table valued function 'dbo.udf_CatsByName_ITVF' because name 'Cats' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Example 3 – Add Encryption

You can also encrypt your functions using the ENCRYPTION argument.

Here’s an example of encrypting the function:

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

GO

Now I’m unable to view the function’s definition.

SELECT definition 
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('udf_CatsByName_ITVF');

Result:

+--------------+
| definition   |
|--------------|
| NULL         |
+--------------+

I also get an error message when trying to script the function’s definition via Azure Data Studio:

No script was returned when scripting as Create on object UserDefinedFunction

Note that an encrypted function’s text is still available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime.