SQL Kiwi (10/18/2011)

---

jared-709193 (10/18/2011)

---

A question that I have is... Is there a performance reason to use or not use a TVF (by this I mean one that uses CREATE FUNCTION versus a correlated subquery). I can see business cases for both as well as personal preference, but I was curious about performance.

Hi Jared,

In-line TVFs are like (parameterized) views in that the definition of the function is expanded into the query text before optimization. A query that uses an in-line TVF will be optimized the same as if you wrote the function text out by hand as a subquery.

Also, you spoke briefly about multi-statement functions, but I am still not 100% clear on this. Does this mean a function that uses CREATE FUNCTION that contains multiple statements in it?

There's quite a lot of good information about functions in Books Online, for example: Types of Functions (link)

A multi-statement TVF definition contains the keywords BEGIN and END (as do scalar user-defined functions) and more than one statement. In-line TVFs use the RETURNS TABLE syntax to introduce a single SELECT. In-line functions do not use the BEGIN/END combination.

Multi-statement functions (and their scalar relations) are often, though not always, a performance drag for many reasons. One reason is that only in-line TVFs are expanded into the parent query for optimization. Scalar UDFs can be especially troublesome because they are executed once per row, in a new T-SQL context, and cannot use parallelism.