July 10, 2011 at 11:44 am
Hi all,
I need your help to clarify the compilation of SP and Function.
My understand is SQL stored procedure gets compiled during the first execution ( but not during the creation, during the creation it checks the syntax and create the object and makes an entry in sys tables sysobjects, syscomments etc) and it saves the execution plan in the cache so that for the further execution it will use that plan (provided we are not forcing any recompilation) But in case of Functions it get compiled for each call.
Is my understanding correct?, In some article I read both SPs and Functions compiles only once and it will use that plan for the further execution if we are not recompiling it, but some other article says as I mentioned above ( ie Function get recompiled in each call).
Can some one let me know which is correct ?.
Also, do we have WITH RECOMPILE option for function? as we have it in Procedure ?
Thanks & Regards,
MC
July 10, 2011 at 12:29 pm
http://msdn.microsoft.com/en-us/library/ms190439.aspx
http://sqltutorials.blogspot.com/2008/03/with-recompile-re-compile-execution.html
Functions are not pre-compiled.
http://www.sql-server-performance.com/2006/stored-procedure-function/
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 10, 2011 at 1:28 pm
As far as I know, it depends on the type of the function. Inline table valued functions don't have cached plans, they're treated just like parameterised views. I think that both scalar functions and multi-statement table valued functions do have plans that are cached.
It's easy enough to test.
http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/
http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2011 at 11:36 am
Hi Welsh Corgi & Gila Monster Thanks for the reply.
Welsh Corgi,
In your reply you have mentioned that the Functions are not pre-compiled,but in the answer section of the link you have provided, it is mentioned that the functions are pre-compiled like stored procedure.
I'm confused again.... :crying:
http://www.sql-server-performance.com/2006/stored-procedure-function/
Gila Monster,
So you mean to say that the multivalued table valued functions are pre-compiled ( i.e they get compiled during the first run ) ?
Thanks & Regards,
MC
July 11, 2011 at 12:42 pm
only4mithunc (7/11/2011)
So you mean to say that the multivalued table valued functions are pre-compiled ( i.e they get compiled during the first run ) ?
As far as I recall, they and scalar functions are. Feel free to check, the posts I referenced discuss some of the cache-monitoring profiler events.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply