Table Valued Function where did my query plan go?

Question

I've just wrapped a complex SQL Statement in a Table-valued function on SQLServer 2000. When looking at the Query Plan for a SELECT * FROM dbo.NewFunc it just gives me a Table Scan of the table I have created.

I'm guessing that this is because table is created in tempdb and I am just selecting from it.

So the query is simply :

SELECT * FROM table in tempdb

My questions are:

Is the UDF using the same plan as the complex SQL statement?

How can I tune indexes for this UDF?

Can I see the true plan?

Answer 1

Multi-statement table valued functions (TVF) are black boxes to the optimiser for the outer query. You can only see IO, CPU etc from profiler.

The TVF must run to completion and return all rows before any processing happens. That means a where clause will not be optimised for example.

So if this TVF returns a million rows, it has be sorted first.

SELECT TOP 1 x FROM db.MyTVF ORDER BY x DESC

Single statement/inline TVFs do not suffer because they are expanded like macros and evaluated. The example above would evaluate indexes etc.

Also here too: Does query plan optimizer works well with joined/filtered table-valued functions? and Relative Efficiency of JOIN vs APPLY in Microsoft SQL Server 2008

To answer exactly: no, no, and no

I have very few multi statement TVFs: where I do, I have lots of parameters to filter inside the UDF.