CODEWITHSUNDEEP
sql-serverstored-procedures
VivekDev
VivekDev

Reputation: 25419

Why is sql stored procedure called a stored procedure

A few days ago I was asked this question(tel intvw) and I was drawn blank. I said execution plan is stored in the server so its called STORED Procedure. But I am not sure I was correct.

My research after that has shown that there is plan cache or procedure cache inside of SQL Server that's dedicated to storing execution plans. In that article there is also reference to what is called compiled plan stub. So it appears that Compiled Plan Stub is first created and then execution plan is created.

So what I wanted to know is briefly

  1. What are the steps that happen when I create a stored procedure?

  2. Why is a Stored Procedure called stored procedure(if the question makes sense)?

If you can refer me to an existing SO question or some other article, that should also be fine.

Upvotes: 4

Views: 888

Answers (2)

TT.
TT.

Reputation: 16137

Why is a Stored Procedure called stored procedure

Because it is a procedure that is stored in a database.

In other languages/environments, procedures that can be executed aren't usually stored. They are compiled in either bytecode or assembler. I.e. the procedure does not exist in its original textual form anymore. The original procedure cannot be retrieved as it was when it was created in those environments (although reverse engineering can retrieve the essence of that procedure).

When you create a stored procedure in SQL Server, it is completely stored in its original full-text form, same indentation, same casing, same lines, including comments and all. You can retrieve the text with which you created the stored procedure in its entirety.

Simplified explanation about executing a stored procedure

When SQL Server wants to execute a stored procedure, it will first check the cache to see if it has been compiled already. If it finds an entry in the cache (in the form of an execution plan) it will use this entry to execute. If it doesn't find an entry it will compile the procedure into an execution plan, store it in the cache for later use, then execute it using the execution plan.

There are cases that force a stored procedure to be recompiled, e.g. when the execution plan cache is cleared (schema changes, statistics updates, ...) or when supplying commands to the compiler that force recompilation (stored procedure WITH RECOMPILE, query option OPTION(RECOMPILE), ...).

Upvotes: 3

TomTom
TomTom

Reputation: 62101

I said execution plan is stored in the server so its called STORED Procedure.

Wrong. Execution plans also are stored on the server for dynamic SQL. I have no real idea why it is called as stored procedure, but the procedure as a whole is stored on the server (code etc.). I can assume this is the reason - but the execution plan (cache) is irrelevant here, because all execution plans are possibly stored there.

For 1: what do you care? Obviously a SQL Statement is executed. There is some parsing, to make sure it is valid. The rest is an implementation detail - and may vary even between versions. I would assume a SP is stored in some level of bytecode - but again, who cares? I do database level development for 25 years and that never even came into my consideration.

Upvotes: 2

Related Questions