Question

The conditions that invalidate a plan include which of the following?

(a) An explicit call to sp_compile

(b) Executing a stored procedure using the WITH COMPILE option

(c) Changes made to a table or view referenced by the query

(d) All of the mentioned

This question was posed to me during a job interview.

The doubt is from Query Plan Reuse topic in chapter Performance Tuning and Optimization of SQL Server

Answer 1

Correct answer is (c) Changes made to a table or view referenced by the query

The explanation is: ALTER TABLE and ALTER VIEW are used to made changes to the table.