Functions in SQL Server 2008

Question

Does sql server cache the execution plan of functions?

Answer 1

According to the dmv yes, http://msdn.microsoft.com/en-us/library/ms189747.aspx but I'd have to run a test to confirm.

Object ID in the output is "ID of the object (for example, stored procedure or user-defined function) for this query plan".

Tested it and yes it does look like they are getting a separate plan cache entry.

Test Script:

create function foo (@a int)
    returns int
as
begin
    return @a
end

The most basic of functions created.

-- clear out the plan cache
dbcc freeproccache
dbcc dropcleanbuffers
go

-- use the function
select dbo.foo(5)
go

-- inspect the plan cache
select * from sys.dm_exec_cached_plans
go

The plan cache then has 4 entries, the one listed as objtype = Proc is the function plan cache, grab the handle and crack it open.

select * from sys.dm_exec_query_plan(<insertplanhandlehere>)

The first adhoc on my test was the actual query, the 2nd ad-hoc was the query asking for the plan cache. So it definitely received a separate entry under a different proc type to the adhoc query being issued. The plan handle was also different, and when extracted using the plan handle it provides an object id back to the original function, whilst an adhoc query provides no object ID.

Answer 2

Yes, see rexem's Tibor link and Andrew's answer.

However... a simple table value function is unnested/expanded into the outer query anyway. Like a view. And my answer (with links) here

That is, this type:

CREATE FUNC dbo.Foo ()
RETURNS TABLE
AS
RETURN (SELECT ...)
GO