WITH RECOMPILE

Question

Hi,team!
I want to know something about WITH RECOMPILE
1]1WITH RECOMPILE is appropriate when only part of the query inside the stored procedure needs to be recompiled. Once the stored procedure is created WITH RECOMPILE, all of the internal queries are recompiled each time the stored procedure is executed.
Can someone give me an example? Thank you in advance.

Answer 1

The WITH RECOMPILE clause can be used into places:

One is when you create a procedure, for instance:

CREATE PROCEDURE MySP WITH RECOMPILE AS

This instructs SQL Server to recompile the entire procedure every time it is called, and never cache the plan.

The other is when you invoke a procedure:

EXEC MySP WITH RECOMPILE

This tells SQL Server to recompile the procedure for this invocation, bypassing what is in cache. I don't if the new plan is stored in cache, or if it is discarded.

Both are old features that have been in the product since at least SQL Server 4.x.

Starting with SQL 2005, there is a better alternative and that the is the statement-level hint OPTION(RECOMPILE):

SELECT ...
FROM   tbl
WHERE Id = @id OR @id NULL
OPTION (RECOMPILE)

This tells SQL Server to recompile this statement every time it is executed. This is more powerful than WITH RECOMPILE for two reasons. One is if this is the only statement in the procedure that calls for a recompile every time, resources are saved by not recompiling the rest. But what is really powerful is that @id can be handled as a constant, and therefore you can get an Index seek when you have an @id, and a table scan when you don't. You cannot get this effect with WITH RECOMPILE, since the compilation occurs when the procedure starts and the run-time value of @id is not known.

So WITH RECOMPILE with CREATE PROCEDURE is in practice a retired feature that you rarely, if ever, have reason to use. And EXEC WITH RECOMPILE has never been very commonly used, I think.

Answer 2

Hi @mille ,

Welcome to Microsoft Q&A!
Here is a related article about RECOMPILE: https://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/
Hope this could give you some help.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.