View Resolution

An SQL statement is resolved to a single execution plan, even when it references a view. Execution plans are not stored for views; the source of the view is stored. When an SQL statement references a view, the parser and optimizer analyze the source of both the SQL statement and the view and resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

For example, consider the following view:

USE Northwind

GO

CREATE VIEW EmployeeName AS

SELECT EmployeeID, LastName, FirstName

FROM Northwind.dbo.Employees

GO

  

Given this view, both of these SQL statements perform the same operations on the base tables and produce the same results:

/* SELECT referencing the EmployeeName view. */

SELECT LastName AS EmployeeLastName,

       OrderID, OrderDate

FROM Northwind.dbo.Orders AS Ord

     JOIN Northwind.dbo.EmployeeName as EmpN

       ON (Ord.EmployeeID = EmpN.EmployeeID)

WHERE OrderDate > '31 May, 1996'

  

/* SELECT referencing the Employees table directly. */

SELECT LastName AS EmployeeLastName,

       OrderID, OrderDate

FROM Northwind.dbo.Orders AS Ord

     JOIN Northwind.dbo.Employees as Emp

       ON (Ord.EmployeeID = Emp.EmployeeID)

WHERE OrderDate > '31 May, 1996'

  

The SQL Server Query Analyzer showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.