Use WITH RECOMPILE Option for Dynamic Stored Procedures

Use WITH RECOMPILE Option for Dynamic Stored Procedures

When a stored procedure is first executed (and it does not have the WITH RECOMPILE option), it is optimized and a query plan is compiled and cached in SQL Server’s buffer. If the same stored procedure is called again from the same connection, the server will use the cached query plan instead of creating a new one, saving time and boosting performance. This may or may not be what you want. If a query is dynamic (the WHERE clauses change from one execution of the stored procedure to the next), it will not be optimized when it is run, and the performance of the query can suffer greatly.

If the query will vary each time it is run from the stored procedure, add the WITH RECOMPILE option when the stored procedure is created. This will force the stored procedure to be re-compiled each time it is run, ensuring the query is optimized each time.

See also  Comparing different methods of testing your Infrastructure-as-Code

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist