Forced Stored Procedure Recompilation

enrique_aeo
enrique_aeo used Ask the Experts™
on
hi experts, i am reading about Forced Stored Procedure Recompilation, but i do not understand the diference between
WITH RECOMPILE at creation
and
WITH RECOMPILE on execution
with realistic scenarios could explain?
recompile.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
RECOMPILE query hint can beaded to a stored procedure as query hint. This allows                                                        only that query (not all) in the stored procedure to recompile. WITH RECOMPILE                                                        option recompiles the whole stored procedure which might not be                                                        needed/beneficial if the stored procedure has many queries and only some of                                                        them need to be recompiled.

http://www.careerride.com/SQL-Server-RECOMPILE-query-hint-and-WITH-RECOMPILE-option.aspx

Author

Commented:
my friend, i need more detail...
what more details, can please elaborate? as main difference between Query hint and "compilation" option while generating SP is, if you use RECOMPILE with query hint at the time of executing SP, SP will recompile from that TSQL only. if you are calling same SP from any other places or front end application SP won't recompile.

If you use "RECOMPILE" at time of creating SP, no matter where you are calling that SP from but it will recompile always. this is the ONLY difference.
Using WITH RECOMILE option at creation time will mean that the stored procedure will be recompiled every time it is executed.
But using WITH RECOMPILE at the time of execution will mean that the stored procedure is recompiled only for that execution, NOT every time.

So if you want that a stored procedure to be recompiled everytime, then use the option at the time of creation.

But if you want to decide about the recompilation during the time of execution depending upon some other conditions (like recompile from one application, but call without recompilation from other), the don't use the option at the time of creation but use during the time of call to stored procedure.

Calling a stored procedure using WITH RECOMPILE option will result in exactly same internal execution as calling a stored procedure created using WITH RECOMPILE option.

If you want that a stored procedure to be recompiled the use the sp_recompile system stored procedure.

So, in bried:

1. recompile a stored procedure EVERY TIME it is executed -- use WITH RECOMPILE at the time of creation.
2. recompile a stored procedure only FOR CURRENT execution -- use WITH RECOMILE while calling the stored procedure.
3. recompile a stored procedure only during NEXT EXECUTION -- use sp_recompile system stored procedure

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial