Common Table Expressions (CTEs) and table-valued functions (TVFs) are features in SQL 2005/2008 that solve a lot of problems for me in terms of functionality and compartmentalizing code.
They also cause issues with performance for larger datasets. There are some ways around this. Hopefully SQL.Next solves some of these issues for us…. in the meantime.
Lately I worked with a customer to help tune his query involving multi-statement table valued function. When using table valued functions, you should be aware of a couple of things
First, there are two type of table valued functions which are inline table valued function (Inline TVF) and multi-statement table valued function (multi-statement TVF). Inline table valued function refers to a TVF where the function body just contains one line of select statement. There is not return variable. Multi-statement table valued function refers to a TVF where it has a return table variable. Inside the function body, there will be statements populating this table variable. In the demo at the end of this blog, there are examples of inline TVF and multi-statement TVF.
Secondly, multi-statement TVF in general gives very low cardinality estimate.
