When working with Microsoft SQL Server, there are two features that can be used to control the compilation of stored procedures and T-SQL statements: WITH RECOMPILE and OPTION (RECOMPILE). In this article, we will explore the differences between these two options and discuss their appropriate usage.

WITH RECOMPILE

The WITH RECOMPILE option is used at the stored procedure level. When this option is specified, the stored procedure is compiled every time it is executed, regardless of the user. This means that any existing execution plan is discarded immediately after execution, and a new plan is generated. There is no caching of the execution plan for future reuse.

OPTION (RECOMPILE)

The OPTION (RECOMPILE) option, on the other hand, is used at the T-SQL statement level. It can be used within a stored procedure or in adhoc T-SQL statements. When this option is specified, a new execution plan is created for that specific statement. Any pre-existing plan, even if it is identical to the new plan, is not used. The new plan is discarded immediately after execution.

Plan Reuse

By default, SQL Server attempts to reuse existing execution plans for stored procedures and T-SQL statements. When executing a stored procedure, SQL Server searches for pre-existing plans in memory. If a plan is found, it is reused. However, if the data in the tables referenced by the stored procedure has changed significantly since the last statistics update, the statement or the entire stored procedure may be recompiled.

Adhoc T-SQL statements follow similar rules for plan reuse. However, since they do not have an ID like stored procedures, existing plans are searched based on the statement structure and the objects contained in the statement. This process may take longer compared to stored procedures.

Choosing the Right Option

When it comes to choosing between WITH RECOMPILE and OPTION (RECOMPILE), it is important to consider the specific scenario and requirements.

For stored procedures, using the WITH RECOMPILE option is generally not recommended. This option erases any previous execution plan and generates a new plan on each execution, which can be unnecessary and expensive.

For adhoc T-SQL statements, using the OPTION (RECOMPILE) option can help reduce the utilization of the plan cache. However, it is important to note that most existing plans do not get reused for adhoc T-SQL statements due to various reasons. It is often more productive to convert adhoc T-SQL statements to stored procedures rather than adding OPTION (RECOMPILE) for each statement in the application.

Conclusion

In conclusion, understanding the differences between WITH RECOMPILE and OPTION (RECOMPILE) in SQL Server is crucial for optimizing query performance. While WITH RECOMPILE can be avoided in most cases, OPTION (RECOMPILE) can be used as a temporary measure for adhoc T-SQL code. However, it is recommended to convert adhoc T-SQL statements to stored procedures for better performance and maintainability.