View Resolution
The Microsoft® SQL Server™ 2000 query processor treats indexed and nonindexed views differently:
- Indexed views are stored in the database in the same format as a table. The query processor treats indexed views the same way it treats base tables.
- Only the source of a nonindexed view is stored. The query optimizer incorporates the logic from the view source into the execution plan it builds for the SQL statement that references the nonindexed view.
The logic used by the SQL Server query optimizer to decide when to use an indexed view is similar to the logic used to decide when to use an index on a table. If the data in the indexed view covers the SQL statement, and the query optimizer determines that an index on the view is the low-cost access path, the query optimizer will choose the index regardless of whether the view is referenced in the WHERE clause. For more information, see Resolving Indexes on Views.
When an SQL statement references a nonindexed view, the parser and query 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 Query Analyzer showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.