Question

Point out the correct statement.

(a) SET SHOWPLAN_TEXT causes Microsoft SQL Server to execute Transact-SQL statements

(b) The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time

(c) The setting of SET SHOWPLAN_XML is set at execute or run time and not at parse time

(d) None of the mentioned

The question was posed to me by my school principal while I was bunking the class.

The origin of the question is Query Execution Plan in portion Performance Tuning and Optimization of SQL Server

Answer 1

Correct option is (c) The setting of SET SHOWPLAN_XML is set at execute or run time and not at parse time

To explain: When SET SHOWPLAN_XML is ON, SQL Server returns execution plan information for each statement without executing it.