This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
.gif)
Inline Table-Valued Functions
Andrew Zanevsky
In this month's column, Andrew Zanevsky continues his discussion on SQL Server 2000's new UDFs (user defined functions) by exploring inline table-valued functions.
In September, I showed you several scalar UDFs. They're what most users had in mind when they asked Microsoft for user-defined functions. But SQL Server 2000 also offers two types of table-valued functions: inline and multi-statement. In this article, I'll look at the former.
There's a cliché that says that old habits die hard, and it seems to me that once one version of a particular software product is learned, we tend to use the same approaches and solutions over and over again—even after new versions provide a better way. Unfortunately, this all-too human trait can be a dangerous trap in our competitive world, because rookies with no experience might actually benefit from increased productivity relative to more experienced "veterans" who remain shackled by old habits.
The best defense is to learn new features ahead of the crowd and apply them efficiently. So, let's learn table-valued functions together and stay in the lead.
Syntax
Let me start by introducing a couple of abbreviations. I'm already tired of typing the words "table-valued functions," so I'll call them TVFs for short. NO, I haven't seen this abbreviation in BOL, or anywhere else for that matter. As I previously stated, there are two types of TVFs—inline and multi-statement—so let's (with all due respect to television channels with similar names) call them ITVFs and MTVFs respectively. 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 shown in last month's article, a scalar function returns a single value. 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! Here's the syntax of the CREATE FUNCTION statement for an ITVF:
CREATE FUNCTION [owner_name.]function_name
( [ { @parameter_name data_type
[ = default ] } [ ,...n ] ] )
RETURNS TABLE
[ WITH < function_option > [ ,...n ] ]
[ AS ]
RETURN [ ( ] select_statement [ ) ]
Remember how last month I warned that you had to use owner_name with function name or it wouldn't work? Well, the same thing is true for an ITVF.
Comparing TVF to tables and views
So what's the difference between a TVF and a table? A TVF produces a dynamic table at the time of execution, depending on parameters. So, is it like a view? Well, yes and no. Like a view, a TVF creates a result set only when it's executed, but, unlike a view, it can be parameterized. If you need a subset of rows from a view, you need to apply a search criteria in a WHERE clause, but an ITVF can accept search criteria as function parameters. Let's look at some examples and see how the same task can be implemented via a stored procedure, view, ITVF, and MTVF.
ITVF examples and advantages
We'll use pubs. 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, state
FROM authors
go
CREATE PROC proc_authors
@au_lname VARCHAR(40)
AS
SELECT au_id, au_fname, au_lname, city, state
FROM authors
WHERE au_lname = @au_lname
go
CREATE FUNCTION dbo.itvf_authors
( @au_lname VARCHAR(40) )
RETURNS TABLE
RETURN
SELECT au_id, au_fname, au_lname, city, state
FROM authors
WHERE au_lname = @au_lname
go
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
My general recommendation is that you consider using ITVFs where ever you use views. Next month, I'll cover MTVFs—the most powerful form of UDFs.
Stored procedures updates
I have several updates to my stored procedures published earlier this year.
First of all, I want to thank everyone who has taken the time to write to me about procedures sp_force_shrink_log and sp_crosstab. I appreciate your words of gratitude as well as problem reports. Several readers have been very helpful in testing the procedures under specific conditions.
I thank Paul McMillan, Eric Hofmann, Sable Harrison, and Joe Song for persistence in shrinking their logs with my procedure. Their e-mails have allowed me to make improvements in the code that's available in the accompanying Download file. I also thank Gordon Young, Dave Audy, and Michael Foley for help with debugging sp_crosstab. The new version (also available for download) is more robust.
If you're using either one of these two procedures, please download updated scripts. They've been tested on SQL Server 7.0. Some of you have made suggestions for enhancements. If I haven't implemented them yet, I might grant your wishes in future columns. And, I know it's also time to upgrade some of my other stored procedures for SQL Server 2000. Fortunately, it should be much easier than it was transitioning from 6.5 to 7.0, so I'll try to get to this task soon.
To find out more about Microsoft SQL Server Professional and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57
Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.
This article is reproduced from the October 2000 issue of Microsoft SQL Server Professional. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Microsoft SQL Server Professional is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-493-4867 x4209.