What is the meaning of "stored procedures are pre-compiled"?

Question

I often heard people saying that stored procedures are pre-compiled. What does it mean?

Actually we write the queries into the stored proc and then compile it. If any syntactic error is there, it complains. So if that is the case then the compilation is happening at that point of time.

Then, what does "Pre" refer to?

Solution 1

They are actually pre-parsed and syntax/semantics checked on CREATE and ALTER

"Compilation" to a query plan happens on demand

For an overview of compilation and re-use, see "Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 "

The terminology (in what you mean) goes back to SQL Server 6.5. The "new" way highlighted in the previous white paper link started with SQL Server 7.0

Solution 2

In Microsoft SQL Server, stored procedures are compiled into a query plan the first time they are run.

At subsequent runs, they are sometimes recompiled from source, but not always. That is why they are called "pre-compiled".

Unless you specify that they should always be recompiled, or when you run sp_recompile to enforce recompilation at the next run time.

Solution 3

The sql content of the stored proc won't be parsed if you run it. The sql parsing can be very time consuming operation.

Comments

[Admin]

Sir, you said pre-parsed. What is pre here. I am very new to this field. so my question may sound to be very silly. But teach me .. i want to learn

[ZygD]

Checking the CREATE/ALTER is different to generating the plan. The syntax etc is checked on CREATE/ALTER. At this point, the stored proc is not compiled. Please see my udate with 2nd link about where "pre compiled" comes from

[Admin]

That means they are never pre compile.. Because "stored procedures are compiled into a query plan the first time they are run" gives me the impression that at the very first time when we write our very first query, only that time only the query plan gets catched. And from next time onwards, when we make modification to out sp, the recompilation happens based on the query plan..correct me if i am wrong!

[littlegreen]

It is true that they are never recompiled after ALTER PROC is run, so it is not technically a pre-compile. But as far as I know they are always recompiled on the first EXEC after ALTER PROC. A cached query plan is only used when there has been no ALTER and no overriding option to force a recompile has been used.

[littlegreen]

See also: stackoverflow.com/questions/1048572/…