In a Relational database management system (RDBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that’s stored in the database in compiled form so that it can be shared by a number of programs. Stored Procedure is an one of the query optimizing object.
First of all why do we need to recompile a Stored Procedure?
Consider that we have a table named “Vembu” and this table does not having any index and we are creating Stored Procedure Named “sp_Vembu_proc”.The “sp_Vembu_proc” is designed to return values from “Vembu” table according to the given condition. After some days we are creating index on “Vembu” table which will sort the values in the table according to a particular column. This index will increase query execution process of table “vembu” but we have created index after creating stored procedure. So, until we recompile a stored procedure it won’t use newly added index.
The situation like above and at some for maintenance purpose, we need to recompile a stored procedure.
Recompiling Stored Procedure
We can recompile a stored procedure in two ways. One is every time when we execute stored procedure and another one is recompiling stored procedure only at necessary condition.
Recompiling every time :
To recompile a stored procedure every time when it is called we can use RECOMPILE keyword during creation of stored procedure .
Example Stored Procedure Creation ,
create proc sp_vembu_proc(@MinID INT, @MaxID INT)
WITH RECOMPILE
As
Begin
select * from dbo.Vembu where ID >=@MinID and ID <=MaxID
Go
End
When the sp_vembu_proc is executed it will recompile every time . Execute query for stored procedure is
“EXEC sp_vembu_proc 332,400 ”.
Recompiling Stored Procedure When Required
To recompile a Stored Procedure at specific time .Need execute the below query .
“EXEC sp_vembu_proc 332,400 WITH RECOMPILE “ while executing stored procedure we need use RECOMPILE Key .
At required time only we have recompile the stored procedure .
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
