Pages

Wednesday, February 8, 2012

Why/ Why not use "WITH RECOMPILE" option?

Once the sproc does not have any syntax errors, it will create entries in sysobjects, sysdepends and syscomments tables. But, it will not compile the query until you execute that.

At the time you execute the proc, SQL Server will create an execution plan and save that in procedure cache for future use.

When the proc executes again, it will re-use the same execution plan, unless otherwise statistics are being changed.

So, if you want to use a new execution plan for each individual execution, use WITH RECOMPILE option in your proc.

Why?
- Optimal use of indexes on columns in a case by case scenario

Why not?
- Improves performance

No comments:

Post a Comment