SET SHOWPLAN_XML (SQL Server Compact)

Causes SQL Server Compact to not execute Transact-SQL statements. Instead, SQL Server Compact caches detailed information about how the statements will be executed in the form of a well-defined XML document.

Syntax

SET SHOWPLAN_XML { ON | OFF }

Remarks

The setting of SET SHOWPLAN_XML is set at execute or run time and not at parse time.

When SET SHOWPLAN_XML is ON, SQL Server Compact caches execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are cached until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_XML is ON, SQL Server Compact displays an error message from any subsequent SELECT statement that involves the same table because the specified table does not exist. When SET SHOWPLAN_XML is OFF, SQL Server Compact executes the statements without generating a report.

SET SHOWPLAN_XML caches information in an XML document after the SET SHOWPLAN_XML ON statement is set. Each batch is reflected in the output by a single document. This cached XML document contains a set of the statements in the batch, followed by the details of the execution steps. The document shows the estimated costs, numbers of rows, accessed indexes, types of operators, join order, and more information about the execution plans. To retrieve the cached plan information, use the SELECT @@SHOWPLAN value.

The document that contains the XML schema for the XML output by SET SHOWPLAN_XML is copied during setup to a local directory on the computer on which SQL Server is installed. It can be found on the drive that contains the SQL Server installation files, at:

\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2004\07\showplan\showplanxml.xsd

Examples

SET SHOWPLAN_XML ON;
-- First query
SELECT [Employee ID] FROM [Employees] WHERE [City]='London';
-- Second query
SELECT [Employee ID] FROM [Employees] WHERE [Country]='USA';
SELECT @@SHOWPLAN;

SET SHOWPLAN_XML OFF;

See Also

Reference

SET STATISTICS XML (SQL Server Compact)

@@SHOWPLAN (SQL Server Compact)