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.

more ▼

asked May 13, 2013 at 05:49 PM in Default

avatar image

0 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered May 13, 2013 at 06:40 PM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

Hi Grant,

Now i understood, thanks for your explanation

May 14, 2013 at 06:03 AM anilvanjre

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.

May 14, 2013 at 10:04 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 13, 2013 at 05:49 PM

Seen: 992 times

Last Updated: May 14, 2013 at 02:56 PM

Copyright 2018 Redgate Software. Privacy Policy