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!