/*
** Indexed_View.SQL
**
** 创建一个初图
** 在Northwind.dbo.[Order Details]创建一个命名为V_Order_Details的初图
** 这个初图中包括一个聚合列—Quantity_SUM
** 然后我们在这个初图上创建索引
** 最近执行查询
*/
USE Northwind
GO
--查看一下[Order Details]表的索引情况
sp_helpindex [Order Details]
---由于[Order Details].PK_Order_Details
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT PK_Order_Details
drop index [Order Details].OrderID
drop index [Order Details].OrdersOrder_Details
drop index [Order Details].ProductID
drop index [Order Details].ProductsOrder_Details
set statistics IO on
SELECT OrderID , SUM(Quantity) AS Quantity_SUM
from dbo.[Order Details]
group by OrderID
--(830 row(s) affected)
--Table 'Order Details'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0.
下面是它的执行计划:
通过上面的图弄我们可以看到现在是在表上执行的表扫描,从IO上可以看到logical reads 9
下面我们将创建一个有索引的初图:
--Create a view on the charge table that
--contains an aggregated colum
CREATE VIEW [V_Order_Details]
WITH SCHEMABINDING
AS
SELECT OrderID , SUM(Quantity) AS Quantity_SUM,COUNT_BIG(*) AS Order_count
from dbo.[Order Details]
group by OrderID
--Create indexes on the view
CREATE UNIQUE CLUSTERED INDEX IXUC_V_Order_Details ON [V_Order_Details](OrderID)
CREATE NONCLUSTERED INDEX IX_V_Order_Details ON [V_Order_Details](Quantity_SUM)
(830 row(s) affected)
Table 'V_Order_Details'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
我们可以看到,虽然我们是从表上进行的以上查询,但是SQL Server的查询优化器自动使用了能够提高查询效率的初图:V_Order_Details。
由此,我们可以在某些应用程序中合理的使用Indexed View来提高应用程序性能。