View Resolution
The SQL Server 2005 query processor treats indexed and nonindexed views differently:
- The rows of an indexed view are stored in the database in the same format as a table. If the query optimizer decides to use an indexed view in a query plan, the indexed view is treated the same way as a base table.
- Only the definition of a nonindexed view is stored, not the rows of the view. The query optimizer incorporates the logic from the view definition 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 all or part of 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 by name in the query. 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 then 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 AdventureWorks
GO
CREATE VIEW EmployeeName AS
SELECT h.EmployeeID, c.LastName, c.FirstName
FROM HumanResources.Employee h
JOIN Person.Contact c
ON h.EmployeeID = c.ContactID;
GO
Based on 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,
SalesOrderID, OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks.dbo.EmployeeName as EmpN
ON (soh.ContactID = EmpN.EmployeeID)
WHERE OrderDate > '31 May, 2002';
/* SELECT referencing the Contact and Employee tables directly. */
SELECT LastName AS EmployeeLastName,
SalesOrderID, OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader AS soh
JOIN AdventureWorks.Person.Contact as c
ON soh.ContactID = c.ContactID
JOIN AdventureWorks.HumanResources.Employee as e
ON e.EmployeeID =c.ContactID
WHERE OrderDate > '31 May, 2002';
The SQL Server Management Studio Showplan feature shows that the relational engine builds the same execution plan for both of these SELECT statements.
Using Hints with Views
Hints that are placed on views in a query may conflict with other hints that are discovered when the view is expanded to access its base tables. When this occurs, the query returns an error. For example, consider the following view that contains a table hint in its definition:
USE AdventureWorks
CREATE VIEW Person.AddrState WITH SCHEMABINDING AS
SELECT a.AddressID, a.AddressLine1,
s.StateProvinceCode, s.CountryRegionCode
FROM Person.Address a WITH (NOLOCK), Person.StateProvince s
WHERE a.StateProvinceID = s.StateProvinceID
Now suppose you enter this query:
SELECT AddressID, AddressLine1, StateProvinceCode, CountryRegionCode
FROM Person.AddrState WITH (SERIALIZABLE)
WHERE StateProvinceCode = 'WA'
The query fails, because the hint SERIALIZABLE that is applied on view Person.AddrState in the query is propagated to both tables Person.Address and Person.StateProvince in the view when it is expanded. However, expanding the view also reveals the NOLOCK hint on Person.Address. Because the SERIALIZABLE and NOLOCK hints conflict, the resulting query is incorrect.
The PAGLOCK, NOLOCK, ROWLOCK, TABLOCK, or TABLOCKX table hints conflict with each other, as do the HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE table hints.
Hints can propagate through levels of nested views. For example, suppose a query applies the HOLDLOCK hint on a view v1. When v1 is expanded, we find that view v2 is part of its definition. v2's definition includes a NOLOCK hint on one of its base tables. But this table also inherits the HOLDLOCK hint from the query on view v1. Because the NOLOCK and HOLDLOCK hints conflict, the query fails.
When the FORCE ORDER hint is used in a query that contains a view, the join order of the tables within the view is determined by the position of the view in the ordered construct. For example, the following query selects from three tables and a view:
SELECT * FROM Table1, Table2, View1, Table3
WHERE Table1.Col1 = Table2.Col1
AND Table2.Col1 = View1.Col1
AND View1.Col2 = Table3.Col2
OPTION (FORCE ORDER)
And View1 is defined as shown in the following:
CREATE VIEW View1 AS
SELECT Colx, Coly FROM TableA, TableB
WHERE TableA.ColZ = TableB.Colz
The join order in the query plan is Table1, Table2, TableA, TableB, Table3.
See Also
Concepts
Resolving Indexes on Views
Designing and Implementing Views