Sample Header Ad - 728x90

Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?

2 votes
1 answer
3327 views
Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates a local temp table (usually by SELECT INTO) or references one created earlier in this procedure. Theoretically, is there any benefit to including either WITH RECOMPILE or OPTION (RECOMPILE) in such a stored procedure? If it helps, assume that I am on a 2016 version of SQL Server. I am ignorant about when and how the presence of temp tables in stored procedures cause recompiles. This question is entirely born of that ignorance. I know the benefits of recompilation in general, but not how or when temp tables cause it.
Asked by J. Mini (1235 rep)
Dec 17, 2023, 12:23 PM
Last activity: May 30, 2024, 07:02 PM