Lets start off with what is a table-valued function (TVF)? A TVF is a dynamic table produced at the time of execution, depending on parameters. Like a view, a TVF creates a result set only when it’s executed, but, unlike a view, it can be parameterized.

You get two types of TVFs, an Inline Table-Valued Function (ITVFs) and Multi-statement Table-Valued Function (MTVFs). I find them easy to remember, think of the “I” in ITVF as 1 (single statement) and the “M” in MTVF as “many” (multiple statements).

As you can imagine, a TVF produces a result set that can be used as a virtual table or view. Yes, you can actually select data from a TVF, or join it with some other tables, views, or even other TVFs. In this post I will go into more detail on ITVFs.

ITVF Examples and Advantages
Suppose that you need to select author id, name, and city from the authors table. Users will often request data by author’s last name. Prior to SQL Server 2000, you could do it either by creating a view or stored procedure. ITVF offers a new option. Consider the following examples and the ease of data retrieval in each case:

CREATE VIEW view_authors
AS
SELECT
    au_id
   ,au_fname
   ,au_lname
   ,city
FROM
    authors
;

CREATE PROC proc_authors @au_lname VARCHAR(40)
AS
SELECT
    au_id
   ,au_fname
   ,au_lname
   ,city
FROM
    authors
WHERE
    au_lname = @au_lname
;

CREATE FUNCTION dbo.itvf_authors
(
    @au_lname VARCHAR(40)
)
RETURNS TABLE
RETURN SELECT
           au_id
          ,au_fname
          ,au_lname
          ,city
       FROM
           authors
       WHERE
           au_lname = @au_lname
;

In order to get a list of authors whose last name is stored in local variable @author, you can use the objects as follows:

SELECT
    *
FROM
    view_authors
WHERE
    au_lname = @author;
EXEC proc_authors @author;
SELECT
    *
FROM
    dbo.itvf_authors(@author)
;

So far, it’s hard to see any advantages to using the ITVF. A stored procedure call is the shortest, but this is the last factor I would consider when choosing an implementation method.

It’s worth mentioning that stored procs are more limiting than views and ITVFs when you don’t need to select all columns. Views and ITVF allow you to list those columns that you want selected. A stored procedure will always select a fixed number of columns unless you write complicated code with dynamic T-SQL. Just consider what you would need to do if a user wasn’t interested in the columns “city” and “state.” The view and ITVF in the example would handle it just fine, but the stored procedure would be virtually useless.

Now suppose that you need to get all titles written by authors whose last name is stored in variable @author. Here’s the code using the view:

SELECT
    a.*
   ,t.title_id
FROM
    view_authors a
    JOIN
    titleauthor  t ON a.au_id = t.au_id
WHERE
    a.au_lname = @author
;

Using the stored procedure for this task is quite inconvenient. Since you can’t join results of a stored procedure directly to another table, you have to save them in a temporary table first:

CREATE TABLE #authors
(
    au_id    VARCHAR(11)
   ,au_fname VARCHAR(20)
   ,au_lname VARCHAR(40)
   ,city     VARCHAR(20)
   ,state    VARCHAR(20)
);
INSERT #authors
EXEC proc_authors @author;
SELECT
    a.*
   ,t.title_id
FROM
    #authors    a
    JOIN
    titleauthor t ON a.au_id = t.au_id;
DROP TABLE #authors
;

The ITVF approach turns out to be more elegant than a view because you don’t need a WHERE clause filter and can specify selection criteria as parameters. It would be even more obvious if you had more parameters:

SELECT
    a.*
   ,t.title_id
FROM
    dbo.itvf_authors(@author) a
    JOIN
    titleauthor               t ON a.au_id = t.au_id
;

ITVfs can be very useful with applications that are doing multiple updates, deletes and inserts into a table. The application can pass through a table with flags to indicate the CRUD operation and then you can run a merge statement.

Hope you enjoyed this post!