Cost of Inline Table Valued function in SQL Server

Question

Is there an inherent cost to using inline-table-valued functions in SQL Server 2008 that is not incurred if the SQL is inlined directly? Our application makes very heavy use of inline-table-valued functions to reuse common queries, but recently, we've found that queries run much faster if we don't use them.

Consider this:

CREATE FUNCTION dbo.fn_InnerQuery (@asOfDate DATETIME)
RETURNS TABLE
AS
RETURN
(
   SELECT ... -- common, complicated query here
)

Now, when I do this:

SELECT TOP 10 Amount FROM dbo.fn_InnerQuery(dbo.Date(2009,1,1)) ORDER BY Amount DESC

The query returns with results in about 15 seconds.

However, when I do this:

SELECT TOP 10 Amount FROM 
(
   SELECT ... -- inline the common, complicated query here
) inline
ORDER BY Amount DESC

The query returns in less than 1 second.

I'm a little baffled by the overhead of using the table valued function in this case. I did not expect that. We have a ton of table valued functions in our application, so I'm wondering if there is something I'm missing here.

Answer 1

In this case, the UDF should be unnested/expanded like a view and it should be transparent.

Obviously, it's not...

In this case, my guess is that the column is smalldatetime and is cast to datetime because of the udf parameter but the constant is correctly evaluated (to match colum datatype) when inline.

datetime has a higher precedence that smalldatetime, so the column would be cast

What do the query plans say? The UDF would show a scan, the inline a seek most likely (not 100%, just based on what I've seen before)

Edit: Blog post by Adam Machanic