Execution Plan for a User Defined Function

SQL Server, T-SQL
When you execute a multi-statement user-defined function you may see an execution plan that looks something like this: It appears as if the cost of the UDF is free. This is especially true if you use the UDF in a query with other objects, such as joining it to actual tables. Since the optimizer always assumes a multi-statement UDF has a single row for statistics estimates, it' frequently displays a low cost. But you know that there’s more going on there, right? It’s a multi-statement UDF because it’s doing a lot of work, but that is not reflected in the execution plan.. or is it? What if we went after the cache? Let’s run this little query: SELECT deqp.query_plan, dest.text, SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1, (deqs.statement_end_offset - deqs.statement_start_offset) /…
Read More

Table Valued Functions

T-SQL
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…
Read More

Multi-Statement Table Value Function Alternative

T-SQL
I was talking with Andrew Novick at the PASS Summit. We ended up talking about multi-statement table valued functions. I was talking about how much the performance of these things is weak (to be kind). He agreed, but suggested an alertnative that might be worth further exploration, if you really think you need multi-statement UDFs. Andrew said that in his testing, using CLR offered a great alernative to using the UDF. I'm still pretty convinced that any type of programming you're doing on the SQL Server end that requires a UDF or CLR is probably either just TSQL gone wrong (see Jeff Moden and the RBAR concept) or it's something that doesn't belong on the SQL Server but instead should be done on the application layer somewhere. Still, it is…
Read More

More on Table Valued Functions

T-SQL
From the hits in the search results, this is a popular topic. That being so, I'd like to redirect you to another blog that has some more detailed tests available. These tests show more of the shortcomings of multi-statement table valued functions. Gail Shaw is an MVP and a regular at SQL Server Central. She's worth tracking and this post shows why. I want to be clear. I'm not suggesting that you never, ever, use multi-statement table valued functions. There may be places where their use is helpful. I'm saying that using them comes with a very heavy cost, so you better be sure that they are in fact needed in the situation, whatever it is. There was a long discussion and debate over at SQL Server Central recently on…
Read More

View vs. Table Valued Function vs. Multi-Statement Table Valued Function

T-SQL
About five years ago, I was checking an app before it went to production. I hadn't seen the app before then and a junior dba had worked with the developers designing and building the app. It didn't use a single stored procedure or view. Instead, it was built entirely of multi-statement UDF's. These UDF's called other UDF's which joined to UDF's... It was actually a very beautiful design in terms of using the functions more or less like objects within the database. Amazing. It also would not, and could not, perform enough to function, let alone scale. It was a horror because they thought they were done and ready to go to production, but no one had ever tested more than a couple of rows of data in any of…
Read More