SHOWPLAN describes the method that is chosen by the SQL Server optimizer to retrieve data. The SQL Server optimizer is an intelligent cost-based query optimizer that attempts to determine the best method for retrieving data that is requested through ad hoc queries. The optimizer attempts to choose the access method that incurs the lowest estimated cost in terms of page I/O. The chosen method is known as the plan. To see the plan, set the SHOWPLAN option ON via the SET statement.
In SQL Server 6.5, implementation of the ANSI SQL joined tables, the UNION in the VIEW statement, the ROLLUP operator, and the CUBE operator have significantly changed the plans that are executed by SQL Server. Furthermore, the examples of output that are produced by SHOWPLAN in "Understanding SHOWPLAN Output" in the Microsoft SQL Server Administrator's Companion have changed significantly. These changes and plans for the new features in SQL Server 6.5 are described in the following sections.