Seeder question:
In SQL 2005 and beyond, there is something called a table-valued function. Apparently there are a couple of different types of table-valued functions. What are they? What are the differences between them? When and how should I use either of them?
I break it down as single-statement table valued user defined functions, also called in-line, and multi-statement table valued user defined functions. The single statement table valued UDF's are effectively views with parameters. Execution of these UDF's will be incorporated directly into the execution plan with the rest of the associated query. Indexes may be used if the query within the UDF is well constructed and these are generally pretty safe constructs to use within your queries.
Multi-statement UDF's are built upon the structure of table variables. These are not incorporated within the rest of the execution plan and usually show up as a table scan against a table with one row. The problem is, your query may have more than one row. When the multi-statement UDF's are used on large sets of data they cause very serious performance problems. They won't scale well, and the performance issues are masked by the apparent low cost of the operations involved. You need to be extremely judicious in the use of multi-statement table valued UDF's.
The two types are inline, and not inline. Many will list "multi-statement", but really the difference comes down to if you have "returns table as return(...)" (inline) or whether you use a table variable definition and use BEGIN ... END in your declaration.
The problem is that the system sees the BEGIN/END and runs the PROCEDURE in a different context. Without it, it takes the subquery in the return() call and can simplify it out.
Scalar functions cannot be inline, which causes problems with them.
Furthermore, non-inline functions do not contribute towards the cost reported by an execution plan. You need to use Profiler to see their effect.
