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
Last activity: May 30, 2024, 07:02 PM