O'Reilly logo

Microsoft® SQL Server™ 2000 Programming by Example by Carlos Eduardo Rojas, Fernando G. Guerrero

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Stored Procedure Recompilation

As you already know, SQL Server creates an optimized execution plan, which is stored in memory, the first time a stored procedure is executed. In general, you want SQL Server to reuse this execution plan for subsequent executions of stored procedures. However, for diverse reasons, sometimes you might want to force SQL Server to modify an execution plan. The reason might fall among one of these: The value of parameters changed significantly, the objects referenced by the stored procedure changed in some way, the data changed significantly or, last but not less important, indexes changed.

There are three ways to explicitly force SQL Server to generate another execution plan:

  • Use the WITH RECOMPILE option when creating ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required