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 “profane” methods in TSQL. These were defined as the methods that any reasonably experienced DBA or database developer will generally proscribe against, such as TSQL cursors, table valued user defined functions (of either variety), CLR, and a few others. The confusion seemed to arise between the statement “there are usually better ways to access the data than to use X” and the statement “THOU SHALT NEVER USE X.” Apparently saying the first somehow causes people to hear the second. I don’t know why and I kind of don’t care. I think there are perfectly valid uses for cursors and WHILE loops and CLR and triggers and UDF’s… But I also know these things have a cost and if you simply don’t use them, there may be the occasional place where you might have recognized a savings, but overall your database will be better off.