Stored Procedure vs Functions compilation and performance difference

Question

Recently I gave an inetrview in which the interviewer asked me to explain the most basic difference between Stored Procedure and UDF's.

I was able to recall a couple of differences as listed here but he didn't accept any of them as the BASIC difference.

Answer according to him was that SP's are only compiled once while UDF's are compiled everytime they are called resulting in UDF's being considerably slower than stored procedure.

Now I have searched but couldn't get a clear cut answer whether this assertion is true. Please verify this.

Answer 1

@mhasan, thanks for referring to my blog-post in your question.

As far a I know Stored Procedures & Functions both have same behavior in terms of compilation & recompilation. Both are not pre-compiled. When you create either one of them they are just parsed and created, but not compiled. Both are compiled when they are executed for the first time. And they could be re-compiled automatically again if there is any change applied to them.

Execute following query after you create a new function:

SELECT objtype, cacheobjtype, usecounts, text 
FROM   sys.dm_exec_cached_plans AS p
       CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE  t.text LIKE '%YourNewFunctionName%' 

You will see only one record, which is the Compiled plan of for this query itself, i.e. an Adhoc Object-Type.

After executing the function rre-execute this query again. You will see more records including the Compiled plan of the Function, which has an Object-Type of Proc.

Hope this helps.

Answer 2

That's a odd statement and as far I know UDF and SP are compiled (and recompiled when it changes) alike. It appear you interviewer mixed UDF with dynamic (non parametric) queries. If someone find a slight info to sustain that argument please report it.