Below Are the some points about Recompile in SQL :
1) If With Recompile clause is added with Stored procedure while its created it will recompile fully at every time its executing
CREATE PROCEDURE <ProcName> WITH RECOMPILE
Select Column1, column2 from Table
2 ) If you need to recompile only subset of stored procedure that is statement then you need to use Recompile Query hint inside the proc (statement).
select * from table OPTION(recompile)
3 ) Another one method you can force the recompilation while executing procedure.
EXEC <ProcName> 'Parameter1', 'Parameter2' WITH RECOMPILE
When stored procedures take parameters whose values differ widely between the executions then Recompile option will help here to Avoid Parameter Sniffing.
Every time plan needs to be created while executing ( CPU bound Process ). Query Elapse time will increase, since new plan creation time also included.
commented on Sep 16 2011 3:47PM