You need to alter stored procedures to use the WITH RECOMPILE option. Which types of stored procedures should you alter? (Each correct answer represents a complete solution. Choose two.)

Question

Options

• ACREATE and ALTER PROCEDURE syntax for CLR Stored Procedure does not have
• BThe RECOMPILE option is ignored for procedures created with FOR REPLICATION.
• CENCRYPTION option and RECOMPILE option can go together.
• DStored procedures that contain queries that use the OPTION (RECOMPILE) hint.

• A
  8% (2)
• B
  12% (3)
• C
  80% (20)

Explanation

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them. This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server is restarted. It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after SQL Server is restarted. In this situation, it can be useful to force the stored procedure to recompile the next time it executes. SQL Server provides three ways to force a stored procedure to recompile: The sp_recompile system stored procedure forces a recompile of a stored procedure the next time that it is run. It does this by deleting the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure is run. Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled every time that it is executed. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled every time that it is executed. You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. RECOMPILE option.

No community discussion yet for this question.