.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Are stored procedures pre-compiled in Sql Server 2005?

Posted By: Jean Paul     Posted Date: February 24, 2011    Points:2   Category :Sql Server
I read in msdn that stored procedures are not pre-compiled from sql server version 7.0

Responses
Author: Amit Mehra             
Posted Date: February 24, 2011     Points: 5   

Hi,

yes it will be pre compiled..if its not dynamic SQL query in stored procedure..Am I right?

Thanks,
Amit
Please "Mark As Answer " if this post help you.
Author: Pandian             
Posted Date: February 24, 2011     Points: 5   

Hi

Pre-compiled is "Reusing an already existing/generated execution plan, not generating the execution plan again and again whenever we run the script / block"

So, The stored procedure also working the same way.

Cheers


If not I then who, If not now then when
Author: Sunil Yadav       [Moderator]   Accepted Answer   
Posted Date: February 24, 2011     Points: 10   

Hi all,

Even i was in a impression that stored procedures are in pre-compiled form.
After reading below posts it is clear that store procedure are not in pre-compiled form.

http://msdn.microsoft.com/en-us/library/aa174792(SQL.80).aspx

http://blog.sqlauthority.com/2009/11/08/sql-server-stored-procedure-are-compiled-on-first-run-sp-taking-longer-to-run-first-time/

http://www.scarydba.com/2009/09/30/pre-compiled-stored-procedures-fact-or-myth/

Please share some interesting article on this topic.

Author: Pandian             
Posted Date: February 24, 2011     Points: 5   

Hi Sunil

YES. I have the same opinion :)

Pre-compiled is "Reusing an already existing/generated execution plan". But the execution plan will be generated on first execution of the procedure...

I mean, the term "Pre-Compiled" We can use like..

- "Compile" on first Execution (or) appriopriate changes happend on stored procedure (or) some SET options

- "Pre-Compiled" on second execution onwards...

Cheers

If not I then who, If not now then when
Author: Sunil Yadav       [Moderator]      
Posted Date: February 24, 2011     Points: 5   

Hi All,

Thanks for sharing your knowledge.

Hope this would be useful to others.

Author: Jean Paul             
Posted Date: February 25, 2011     Points: 5   

Thank You Sunil.. You made me in the right position

It is concluded that Stored Procedures are not precompiled after version Sql Server 6.5

Jean Paul http://jeanpaulva.com/
Author: sriram             
Posted Date: October 27, 2011     Points: 20   

yes Stored Procedure are Precompiled Execuction in sql server 2005.it will reduce the client server network traffic,reduce the bandwidth because its already compiled..

Regards

Sriram.R



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend