Performance of Inline-TVF vs. Views

Question

I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together inside the TVF [fnCarBrands].

These TVFs are then called by other TVFs to do further processing and reporting. So I might take my function [fnCarBrands] and join to the table [Purchase Year] to form a function [fnCarBrandHistory]. And so on for several layers of TVFs.

I could probably get the same functionality using views, since my inline TVFs are really just joins of tables and other TVFs.

How does the performance of inline TVFs written in this way compare with views?

Answer 1

The query optimizer treats an inline table valued function exactly like a view:

CREATE FUNCTION dbo.InlineUdf(@arg1 int)
RETURNS TABLE
AS
RETURN 
(
    ... your query here ...
);

A multi-statement table-valued function is run more like a stored procedure. They typically have to be executed multiple times, rather than be folded into the main query:

CREATE FUNCTION dbo.MultiStatementUdf (@col1 int)
RETURNS @result TABLE 
(
    id int primary key NOT NULL,
    ... 
)
AS
BEGIN
   DECLARE @var1 int
   set @var1 = 42

   INSERT @result
   SELECT ...
   RETURN
END;

Answer 2

You'll need to create similar views to the functions and query each looking at the execution plan to see what's going on with each.

Answer 3

Of course creating views that call other views is a performance killer too. Do not go down that route. Write the queries you need and don't use either TVFs or views if you want performance. It is the layering that is creating the problem, this is almost always a bad thing to do when querying a database and you can quickly end up hitting the limit of the number of tables you can reference too, especially since you often end up referencing the same tables in different layers. Further, while this seems as if it woudl be easier to maintain, it is not. Try debugging or adding a column due to a new requirement when the layer you need to fix is at the bottom.