Inline Table-Valued Functions (TVFs)

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!

GenesisOne™ T-SQL Source Code Unscrambler™

Have you ever worked with code that is impossible to read and decipher? You could use SQL Prompt which will format the code into a readable format, however what if there is so much going on you are still unable to decipher what is happening?

Recently I came across this problem where a function was doing so many things I couldn’t tell where it started and where it ended. There is an amazing tool that was super helpful, it is the T-SQL Source Code Unscrambler that is offered by GenesisOne.

This was a lifesaver for me, saving many hours of trying to figure out code. This tool allows you to connect to your SQL Server database and view any tables, views, stored procedures and functions.

This tool allowed me to select the function that was a mess and in the diagram view I could see what the function was trying to do. It shows if there are conditions, variables and the flow of the function.

This tool is also brilliant for doing technical documentation with diagrams of the systems and data flow in stored procedures and functions.

Below are a few screen shots of the different views and information this tool provides:

Diagram View:

Table View:

Summary View:

Stored Procedure View:

Dependency View:

Graphical View of Dependencies:

How to Export:

For a full list of all the amazing features that comes with this application, click here.

Hope you all decide to try out this tool and then get it after you realise how much time it shall save you in development!