Programming in MS SQL Server 2012

WITH RECOMPILE Option



Stored Procedure is a group of Transact-SQL statements that compiled into a single query execution plan based on database structure and predefined stored procedure's parameter(s) information.

By default, SQL Server automatically recompiling existing stored procedures when restarted server or update referenced table structure of stored procedure. If parameter values on the stored procedure are frequently atypical, forcing a recompile of the stored procedure that creates a new query execution plan based on different parameter values to improve stored procedure performance.

SQL Server provides three ways to recompile a stored procedure:

01. WITH RECOMPILE statement is used within stored procedure itself.

02. RECOMPILE keyword is used when execute user-defined stored procedure.

03. sp_recompile system stored procedure is used when execute user-defined stored procedure.

The following example describes CREATE PROCEDURE including WITH RECOMPILE Option in the parameter section. This option causes the stored procedure to execute more slowly, because the stored procedure must be recompiled each time it is executed.

-- Example 118 --

 

USE EBusiness

GO      

 

IF EXISTS (SELECT *

           FROM sysobjects

           WHERE name = N'dbo.usp_EmployeePhoneListRecompile' AND type = 'P')

           DROP PROCEDURE dbo.usp_EmployeePhoneListRecompile

GO

 

CREATE PROCEDURE dbo.usp_EmployeePhoneListRecompile

@Phone varchar(50)

WITH RECOMPILE

AS

SELECT

   EMP.Emp_Code,

   EMP.Emp_Name,

   EMP.Phone,

   EMP.Dept_Code,

   DEPT.Dept_Name FROM EMP

INNER JOIN DEPT

ON EMP.Dept_Code = DEPT.Dept_Code

WHERE EMP.Phone LIKE @Phone


Query Output Screen



The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

-- Example 119 --

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '%'

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '10%'

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '104%'


Query Output Screen



The WITH RECOMPILE keyword forces a recompile of a stored procedure the next time it is run. The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

Query Output Screen



The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run. The following Transact-SQL statements are used to retrieve employee(s) information based on given input parameter unique pattern data values.

-- Example 120 --

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '%' WITH RECOMPILE

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '10%' WITH RECOMPILE

 

EXECUTE EBusiness.dbo.usp_EmployeePhoneListRecompile '104%' WITH RECOMPILE

-- Example 121 --

 

EXECUTE sp_recompile N'EBusiness.dbo.usp_EmployeePhoneListRecompile'


Query Output Screen



* * * * *


Email Your Comment To AUTHOR