通过 SQL Server 2005 索引视图提高性能(8)

通过包含视图定义中的 SumPrice 和 Count 列以便计算查询中的 AVG，该视图将满足“查询 6”的条件。

CREATE VIEW View4 WITH SCHEMABINDING AS 
SELECT p.Name, od.ProductID, 
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice, 
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count 
FROM Sales.SalesOrderDetail AS od, Production.Product AS p 
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10
GROUP BY p.Name, od.ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)

查询 8

“视图 4”上相同的索引也将用于在其中添加对表 Sales.SalesOrderHeader 的联接的查询。该查询满足条件：查询 FROM 子句中所列的表是索引视图的 FROM 子句中的表的超集。

SELECT p.Name, od.ProductID, 
AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) AS AvgPrice, 
SUM(od.OrderQty) AS Units 
FROM Sales.SalesOrderDetail AS od, Production.Product AS p, 
     Sales.SalesOrderHeader AS o 
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID 
     AND od.UnitPrice > 10 
GROUP BY p.Name, od.ProductID

最后两个查询在“查询 8”的基础上进行了修改。每个修改后的查询都违反了优化器的条件之一，并且不同于“查询 8”，无法使用“视图 4”。

查询 8a

“查询 8a”(Q8a) 无法使用索引视图，因为 WHERE 子句无法将视图定义中的 UnitPrice > 10 与查询中的 UnitPrice > 25 相匹配，而且 UnitPrice 未出现在视图中。查询搜索条件谓词必须是视图定义中的搜索条件谓词的超集。

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) 
     AvgPrice, SUM(od.OrderQty) AS Units 
FROM Sales.SalesOrderDetail AS od, Production.Product AS p, 
     Sales.SalesOrderHeader AS o 
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID 
     AND od.UnitPrice > 25 
GROUP BY p.Name, od.ProductID

查询 8b

注意，表 Sales.SalesOrderHeader 不加入索引视图 V4 定义。尽管这样，在该表上添加一个谓词将不允许使用索引视图，因为所添加的谓词可能会更改或消除加入下方“查询 8b”所示的聚合的其他行。

SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount)) 
     AS AvgPrice, SUM(od.OrderQty) AS Units 
FROM Sales.SalesOrderDetail AS od, Production.Product AS p,
     Sales.SalesOrderHeader AS o 
WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID 
     AND od.UnitPrice > 10 AND o.OrderDate > '20040728' 
GROUP BY p.Name, od.ProductID

视图 4a

“视图 4a”通过将 UnitPrice 列包含在选择列表和 GROUP BY 子句中，扩展了“视图 4”。“查询 8a”可使用“视图 4a”，因为将进一步筛选 UnitPrice 值（已知大于 10）以便只留下大于 25 的值。以下是间隔归入的一个例子。

CREATE VIEW View4a WITH SCHEMABINDING AS 
SELECT p.Name, od.ProductID, od.UnitPrice,
SUM(od.UnitPrice*(1.00-UnitPriceDiscount)) AS SumPrice, 
SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count 
FROM Sales.SalesOrderDetail AS od, Production.Product AS p 
WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10 
GROUP BY p.Name, od.ProductID, od.UnitPrice
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd 
     ON View4a (Name, ProductID, UnitPrice)

视图 5

“视图 5”在其选择和 GROUP BY 列表中包含一个表达式。请注意，LineTotal 是一个计算列，因此本身是一个表达式。反过来，该表达式嵌套在对 FLOOR 函数的调用中。

CREATE VIEW View5 WITH SCHEMABINDING AS
SELECT FLOOR(LineTotal) FloorTotal, COUNT_BIG(*) C
FROM Sales.SalesOrderDetail
GROUP BY FLOOR(LineTotal)
GO
CREATE UNIQUE CLUSTERED INDEX iView5 ON View5(FloorTotal)

查询 9

“查询 9”在其选择和 GROUP BY 列表中包含表达式 FLOOR(LineTotal)。通过对 SQL Server 2005 中表达式的视图匹配的新扩展，该查询使用“视图 5”上的索引。

SELECT TOP 5 FLOOR(LineTotal), Count(*)
FROM Sales.SalesOrderDetail
GROUP BY FLOOR(LineTotal)
ORDER BY COUNT(*) DESC

视图 6

“视图 6”存储月末三天中有关线项目的信息。这样可将这些行聚集在少量页面上，从而可以迅速应对这些天里对 Sales.SalesOrderDetail 的查询。

CREATE VIEW View6 WITH SCHEMABINDING AS
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, 
     ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, 
     ModifiedDate
FROM Sales.SalesOrderDetail
WHERE ModifiedDate IN ( convert(datetime, '2004-07-31', 120),
                        convert(datetime, '2004-07-30', 120), 
                        convert(datetime, '2004-07-29', 120) )
GO
CREATE UNIQUE CLUSTERED INDEX VEndJulyO4Ind 
     ON View6(SalesOrderID, SalesOrderDetailID)
GO

查询 10

下面的查询可匹配“视图 6”，同时系统可生成一个计划，用于扫描视图上的 VendJuly04Ind 索引，但不扫描整个 Sales.SalesOrderDetail 表。此查询还说明了表达式等价（由于查询中日期的顺序不同于视图，而且数据格式也不同）和谓词归入（由于查询要求将结果的子集保存在视图中）。

SELECT h.*, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
   ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, d.rowguid,
   d.ModifiedDate
FROM Sales.SalesOrderHeader as h, Sales.SalesOrderDetail as d
WHERE (d.ModifiedDate = '20040729' OR d.ModifiedDate = '20040730')
and d.SalesOrderID=h.SalesOrderID

视图 7

开发人员有时还会发现使用索引视图强制专门的完整性约束很方便。例如，可通过索引视图强制约束：“除非列中存在多个 0 值，否则表 T 的列 a 就是唯一的”。下方索引视图“视图 7”就强制了这一约束。如果运行下面的脚本，其将成功运行直至最终的插入操作。该语句被禁止，因为其添加了一个非零重复值。

USE tempdb
GO
CREATE TABLE T(a int)
GO
CREATE VIEW View7 WITH SCHEMABINDING
AS SELECT a
FROM dbo.T