Stored Procedure vs Functions compilation and performance difference
sql-server, stored-procedures
Solution
@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.
Problem
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.