When SQL Server added functions to the T-SQL language, many developers were excited about the ability to finally use functions to build modular code. This was a major advance for T-SQL, but it didn't quite work out as well as Microsoft would have hoped. There are plenty of articles on UDFs, many of which will explain that unlike functions in other languages, some of the functions in T-SQL aren't optimized to execute in an efficient fashion. This is true of scalar and multi-statement table user-defined functions.

Inline Table Valued Functions (iTVF) do tend to perform much better. These functions have a certain structure and a number of restrictions, which are covered in this article. iTVFs are used in many queries where a scalar function might perform pooly. They are also used to implement Row-Level Security.

Creating an iTVF

Let's begin by creating a quick iTVF. We'll use the WideWorldImporters sample database. I'll create an iTVF that takes a parameter and returns some information about packaging. I'll use the standard CREATE FUNCTION syntax, but there are a few differences that you might not expect.

CREATE FUNCTION Warehouse.StockPackaging
    (@StockItemID AS INT)
RETURNS TABLE
AS
RETURN ( SELECT 
          si.StockItemID ,
          si.StockItemName ,
          c.ColorName ,
          InnerPackage = pt.PackageTypeName ,
          OuterPackage = pt1.PackageTypeName ,
          si.UnitPrice
          FROM Warehouse.StockItems AS si
           INNER JOIN Warehouse.Colors AS c ON si.ColorID = c.ColorID
           INNER JOIN Warehouse.PackageTypes AS pt
           ON pt.PackageTypeID = si.UnitPackageID
           INNER JOIN Warehouse.PackageTypes AS pt1
           ON pt1.PackageTypeID = si.OuterPackageID
   WHERE si.StockItemID = @StockItemID )

If we examine the code, notice that there is no BEGIN..END in this function. That's because an iTVF is an inline query that returns a table to the caller. We can have parameters, but they are used in a SELECT statement that is our return clause. The SELECT is essentially inline for our function, hence the name.

The basic structure of our function is that we

• define the function with CREATE FUNCTION, with a name
• Include parenthsis after the name. Optionally parameters are added in a comma separated list
• Use the RETURNS TABLE AS keywords
• Follow the AS with a RETURN and parenthesis
• Inside the final parenthesis is a single query.

As a another example, here's a minimal iTVF, albeit a silly one.

CREATE FUNCTION dbo.JustTwo ()
RETURNS TABLE
AS
RETURN ( SELECT 
          two = 2
)
GO

This returns just a single column, single row table.

Not very useful, but that's a minimal function: a name, no parameters, the proper keywords, and a basic SELECT statement. That's about the minimum amount that you include in an iTVF.

From here, we can add more details back in. Parameters are often used, so let's add a simple one back. If I want to give a result based on the parameter, I can do this:

CREATE FUNCTION dbo.JustTwo (@value INT)
RETURNS TABLE
AS
RETURN ( SELECT 
          answer = @value * 2
)
GO

Still silly, but this uses the parameter to get a value. The results here are easy to see (and guess):

Parameters follow the same rules as they would for any function. Each parameter must have a unique name and data type. They can have default values, but all arguments for a function are required when calling the function. If a default value is to be used, the DEFAULT keywork is used in the function call. An example is a query that might find all the recent birthdays before a particular date. I could write an iTVF like this:

CREATE OR ALTER FUNCTION dbo.RecentBirths
(@birthdate DATE, @days INT = 30)
RETURNS TABLE
AS
RETURN
    (
        SELECT
             b2.cust_fname
           , b2.cust_lname
           , b2.cust_dob
           , DaysAgo = DATEDIFF(DAY, @birthdate, b2.cust_dob)
        FROM dbo.birthdays AS b2
WHERE b2.cust_dob > DATEADD( DAY, 0-@days, @birthdate)
AND b2.cust_dob <= @birthdate
    );
GO

This would return those rows that were within @days number of days before @birthdate. If I didn't know the default,but wanted to use it, I could call the function like this:

An iTVF can be created in any way that meets the criteria laid out above, as along as the contents of the function are expressed as a single query statement. No assignment, IF THEN, or other statements can be included.

Using iTVFs

An iTFV is used just like a table, in any place where a table can be used. One example might be in a join inside a query. I can take my function above and use it in an inner join clause as shown here:

SELECT
     *
FROM dbo.OrderHeader AS oh
 INNER JOIN dbo.RecentBirths('20161225', DEFAULT) rb
 ON DATEPART(MONTH, oh.OrderDate) = DATEPART(MONTH, rb.cust_dob)
 ;

This gives me these results:

Notice that the columns returned by the iTVF can be used like the columns in any other table. The function, with parameters, is included in place of a table in the INNER JOIN clause just as any other table might be used here.

The iTVF can also be used as it might be with the APPLY operator. In the example below, I'll take a date from the dbo.OrderHeader table and use that as a parameter to the iTVF function, which will then be used in that query. This will return a series of orders that have a person that has a birthdate within 30 days of the order. I could also use some other value, such as the value of the order, to decide how many days to include. Here you can see I've included a CASE statement in as my parameter.

The number of days used to process the function varies by the value of the order. You can see in the query before this one that were were two other birthdays that were within 24 days of the order, but this CASE statement has filtered them out.

Conclusion

There are many creative ways that I have seen iTVFs used to solve various query problems while providing a reuseable set of code in a function. This article has given the basic view of how an iTVF is structured and how you might write one. As long as you can structure your query as a single statement with parameters, you can include it in an iTVF.

This is a structure that you should consider using in your applications where you need to provide resuable code, but do not want the performance penalty of a scalar function or a multi-statement TVF. I would urge you to practice writing these functions and learn how to query them along with other tables.