When you create a stored procedure you provide an option with RECOMPILE
What does this do?
When ever you create a stored procedure, its compiled and the Query execution plan is stored in Cache. So when ever this stored procedure is used, SQL Server fetches this stored Query Execution plan from cache and executes.
So when you create procedure with Re-Compile option, when you execute this procedure, the procedure is compiled each time its run and new query plan is created for the same
SQL Server Interview Questions · · · ·