You are a database developer on an instance of SQL Server 2008 named Srv1. You need to define a stored procedure that will be used by one of your applications. The stored procedure executes multiple DML statements to perform several table updates.
You want to ensure that SQL Server never caches query execution plans when executing this stored procedure.
Which action should you take?
A. Create the stored procedure with schema binding.
B. Call the sp_recompile system stored procedure.
C. Include the WITH RECOMPILE option when creating the stored procedure.
D. Use the RECOMPILE query hint within the stored procedure.
Correct Answer: C
Explanation/Reference:
You should include the WITH RECOMPILE option when creating the stored procedure. You can use the WITH RECOMPILE clause when creating a stored procedure. This will force SQL Server to recompile the stored procedure each time it is called. SQL Server will not cache and reuse execution plans. Using the WITH RECOMPILE clause to force compilation can improve performance for stored procedures that accept varying input parameters that might result in different execution plans. However, you should note that using WITH RECOMPILE to always force stored procedure recompilation might decrease performance.
You can use the RECOMPILE query hint to force recompilation each time a single query is executed so that the cached plan for the query will not be used.
However, in this scenario, the stored procedure contains multiple queries, and you would have to specify the query hint for each query.
The sp_recompile system stored procedure will force recompilation of a stored procedure, but only the next time the stored procedure is executed.
If you include the WITH SCHEMABINDING clause in a CREATE PROCEDURE statement, an error similar to the following occurs:
Msg 487, Level 16, State 1, Procedure MyProc, Line 3
An invalid option was specified for the statement "CREATE/ALTER PROCEDURE".
Schema binding is used when creating a view or function by including the WITH SCHEMABINDING clause in the CREATE VIEW or CREATE FUNCTION statement. Using schema binding ensures that no objects on which the view or function depends are dropped or modified in a way that might affect the referencing object’s definition. For example, including the WITH SCHEMABINDING clause in a CREATE VIEW statement ensures that no base tables on which the view is based are dropped or modified in a way that might affect the view’s definition. This ensures that users cannot drop or modify base tables in such a way that the view becomes unusable. To drop base tables or make such modifications, you would first need to drop the view, alter the view omitting SCHEMABINDING, or alter the view definition to remove any unwanted dependencies. Similarly, you can include the WITH SCHEMABINDING clause when creating a function to ensure that objects referenced within the function are not changed in such a way that would render the function unusable.
