asked
Related to Stored Procedure & User Defined Function's Pre-Compiled Concepts
Hi All, Need Help on Bellow Mentioned Doubts Q 1: What happens when we Submit / Execute the a Sql Statement (For Ex : select * from person ) i.e. what are the execution steps taken by the Sql Server behind the Scene. Q 2 : What is Pre-Compiled Concept in Sql Server ? Q 3: Is Stored Procedures in Sql Server are Pre-Compiled? Q 4: Is User Defined Functions in Sql Server are Pre-Compiled? Q 5: If UDF’s are not Pre-Complied , why there are not Pre- Compiled? I Googled the above Q 3 & Q 4 , some where they say Stored Procedures are Pre-Complied but User Fined Functions are not Pre-Complied they compile only in execution time ( which indirectly say’s that UDF’s are compiled every time ? And Any Performance Degradation of UDF’s against SP’s) I am really bit confusing between both SP’s and UDF’s Pre-Compilation concepts.
When you submit a query it goes through the optimization process then the storage engine retrieves the data. The optimization process includes validating the syntax, ensuring that all the objects exist, checking to see if the plan is already in cache and doesn't need to be optimized and then the full optimization process. There is no pre-compiled anything within SQL Server except SQL Server, so questions 2-5 the answers are all the same. Nothing is pre-compiled, not triggers, not stored procedures, not user defined functions. UDFs are not compiled every time. Any query within SQL Server gets compiled and stored in cache, except queries that are marked as RECOMPILE. They won't be stored at all. There are some other exceptions around this, but for a basic conversation, it's accurate enough.
Good. Thanks for the feedback @anilvanjre. Any other questions, always come back. If any answers on the site are helpful, indicate that by clicking on the thumbs up next to those answers. If an answer solves your question, also indicate that by clicking on the check box next to that answer.
14 People are following this question.
