Table Valued Functions

January 7, 2009 at 7:43 am (TSQL) (, , )


I’ve been blogging for a whole nine months now. I decided to look back and see what’s bringing people to the site. The number one search phrase is “sql server 2005 service pack 3” but the overwhelming topic that most people are using to get to the site are user defined functions, specifically multi-statement table valued user defined functions.

It’s completely understandable. Ever since I first saw these things in use back in SQL Server 2000, I thought they were slick. Unfortunately appearances can be deceiving. The reason so many people are searching out information on these things is because they just don’t work very well. SQL Server can’t create statistics on the tables generated through the multi-statement UDF. Because it has no statistics to work with, the query optimizer assumes that each of these tables has a single row. Creating execution plans for only a single row of data works well when there is only a single row, or just a few rows, of data. But when you have hundreds, thousands, or more, it breaks down very quickly.

Because of this rather severe short coming, I recommend avoiding the use of multi-statement table valued functions. There are any number of tests (look through Gail’s blog, just for starters) proving this for those who are skeptical and think that, if they just adjust the right little bit of the query, it’ll move millions of rows through 8 layers of functions lickety split. It won’t. It can’t. Stop trying.

The single statement functions, parameterized views as they’re sometimes referred to, work very well, so feel free to use them if they’ll help your code. They resolve out, like views, into actual execution plans based on the statistics within the database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: