Significance of WITH RECOMPILE option when creating a stored procedure

Next>>         SQL Server tutorial  

SQL Server - significance of WITH RECOMPILE option - Feb 27, 2010 at 11:50 AM by Shuchi Gauri

Explain the significance of WITH RECOMPILE option when creating a stored procedure.

When a stored procedure is executed for the first time, SQL Server optimizes and compiles it. A query plan is created and stored in cache for the stored procedure. On subsequent executions, SQL Server looks in the cache for the stored procedure, if the stored procedure is found it is executed without compilation. It only compiles it if the stored procedure is not found in the cache. This slows down the execution of the stored procedure if it is executed multiple times, each time with a separate parameter passed to it, since it follows the same old query plan. WITH RECOMPILE option allows creation of a new query plan for the stored procedure, every time it is called. This speeds up the execution of the stored procedure significantly.

SQL Server - significance of WITH RECOMPILE option - May 05, 2009 at 22:00 PM by Rajmeet Ghai

Explain the significance of WITH RECOMPILE option when creating a stored procedure.

When a stored procedure is created using WITH RECOMPILE option, the execution plan for the procedure is not cached. Instead, it is recompiled each time the procedure is executed. WITH RECOMPILE option should be used when the stored procedure takes a variety of parameters.

Syntax:

CREATE PROCEDURE proc_name WITH RECOMPILE
{
     Procedure definition
}

SQL Server - significance of WITH RECOMPILE option - June 21, 2009 at 09:00 AM by Amit Satpute

Explain the significance of WITH RECOMPILE option when creating a stored procedure.

• A stored procedure with the WITH RECOMPILE option indicates that SQL Server does not cache a plan for this stored procedure.
  Therefore, the stored procedure is recompiled each time it is executed.
• This option should be used when stored procedures take widely varying parameters that result in the creation of different execution plans every time.
• The drawback is that the stored procedure is caused to execute more slowly as it needs to be recompiled every time for execution.

Next>>

Also read

Explain the difference between RECOMPILE query hint and WITH RECOMPILE option.

When a stored procedure is created using WITH RECOMPILE option, the execution plan for the procedure is not reused. It is created each time during execution............

Explain Full-Text Query in SQL Server.

Answer - SQL Server supports searches on character string columns using Full-Text Query......

What security features are available for stored procedures?

Answer - Database users can have permission to execute a stored procedure without being......

What are the differences among batches, stored procedures, and triggers?

Answer - A batch is a group of one or more SQL statements. SQL Server compiles the statements......