SQL Server Performance

Table Valued function

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by atulgoswami, Oct 25, 2010.

  1. atulgoswami New Member

    I am not sure how table valued function do well when it comes to performance. Or is there any specific performance benefit with table valued functions over View/CTE?I checked the execution plan and could not see any additional activity. We are in SQL 2008 R2 and i am thinking, would be a good approach to convert the table valued function to SP, which will return table variable to calling SP?Thanks
  2. FrankKalis Moderator

    Inline table-valued function are pretty much like views. The execution plan is expanded out into the main plan and then optimised. Table-Valued functions used to be kind of a black box to the query optimiser. It couldn't optimise them as much as may be possible. So there was a chance to get a suboptimal plan when using TVF's. I'm not sure if this has changed with SQL Server 2008 R2, but as you mention I would probably go for a table valued parameter anyway in that version. But in the end should it be easy to set up a small comparison and go with whatever seems to be best in your environment.

Share This Page