Obviously, were talking about multi-statement functions, since in-line functions are just views in the end.
My gut feeling for this is “yes”, but I wanted to be absolutely certain. So here goes:
- Create a function in Adventureworks
- Use that function in a SELECT statement
- Check if a plan exists in the plan cache for above
USE Adventureworks
GO
IF OBJECT_ID(‘fn’) IS NOT NULL DROP FUNCTION fn
GO
CREATE FUNCTION fn(@rg uniqueidentifier)
RETURNS @tbl TABLE(SalesOrderDetailID int NOT NULL PRIMARY KEY, OrderQty smallint NOT NULL)
AS
BEGIN
INSERT INTO @tbl(SalesOrderDetailID, OrderQty)
SELECT SalesOrderDetailID, OrderQty FROM sod WHERE rowguid = @rg
RETURN
END
GO
SELECT * FROM fn(‘80667840-F962-4EE3-96E0-AECA108E0D4F’)
GO
SELECT cp.cacheobjtype, cp.plan_handle, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE qp.objectid = OBJECT_ID(‘fn’)
IF OBJECT_ID(‘fn’) IS NOT NULL DROP FUNCTION fn
Note the execution plan in XML format picked up from sys.dm_exec_query_plan. If you executed the query in grid format, you can cklick on it, and then save the XML as a file. Rename the file extension to .sqlplan and open that file in SSMS. You can now see the plan for this function graphically.