只存储非索引视图的源。查询优化器将视图源中的逻辑纳入执行计划,而该执行计划是它为引用非索引视图的 SQL 语句生成的。
SQL Server 查询优化器用于决定何时使用索引视图的逻辑与用于决定何时在表上使用索引的逻辑相似。如果索引视图中的数据包括 SQL 语句,且查询优化器确定视图上的某个索引是低成本的访问路径,则不论 WHERE 子句中是否引用了该视图,查询优化器都将选择此索引。有关更多信息,请参见解析视图上的索引。
USE Northwind
GO
CREATE VIEW EmployeeName AS
SELECT EmployeeID, LastName, FirstName
FROM Northwind.dbo.Employees
GO
在此视图中,这两个 SQL 语句在基表上执行相同的操作且生成相同的结果:
/* 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'