SQL Create Index on View with SUM() in Select

Question

使用SQL2005，我需要创建一个视图来显示select语句，如下所示：

select c1.personid, Max(c1.call_Date) Call_Date, Sum(s1.quantity) Num_Boxes,
from dbo.kits_dropped s1
inner join dbo.calls c1 on(c1.callsid = s1.callsid)
Where s1.product_name = 'Product X'
GRoup by c1.personid

如何在上述视图中的personID上创建索引？

谢谢!

Answer 1

这里有一个关于创建indexed views in SQL 2005的指南。

这可能会帮助您入门，它显示了一个在personid上具有聚集索引的视图

CREATE VIEW MyView WITH SCHEMABINDING AS 

SELECT 
        c1.personid, 
        MAX(c1.call_Date) AS Call_Date, 
        SUM(s1.quantity) AS Num_Boxes,
FROM dbo.kits_dropped s1
INNER JOIN dbo.calls c1 ON (c1.callsid = s1.callsid)
WHERE s1.product_name = 'Product X'
GROUP BY c1.personid

GO

CREATE UNIQUE CLUSTERED INDEX MyViewInd ON MyView(personid)

如果您在上面遇到任何问题，请发表意见。