Maintaining Indexes - Microsoft SQL Server 2012 Performance Tuning - page 243

CREATE VIEW POView
WITH SCHEMABINDING
AS
SELECT
POH.PurchaseOrderID
,POH.OrderDate
,EMP.LoginID
,V.Name AS VendorName
,SUM(POD.OrderQty) AS OrderQty
,SUM(POD.OrderQty*POD.UnitPrice) AS Amount
,COUNT_BIG(*) AS Count
FROM
[Purchasing].[PurchaseOrderHeader] AS POH
JOIN
[Purchasing].[PurchaseOrderDetail] AS POD
ON
POH.PurchaseOrderID = POD.PurchaseOrderID
JOIN
[HumanResources].[Employee] AS EMP
ON
POH.EmployeeID=EMP.BusinessEntityID
JOIN
[Purchasing].[Vendor] AS V
ON
POH.VendorID=V.BusinessEntityID
GROUP BY
POH.PurchaseOrderID
,POH.OrderDate
,EMP.LoginID
,V.Name
GO

--creating clustered Index on View to make POView Indexed View
CREATE UNIQUE CLUSTERED INDEX IndexPOView ON POView
(PurchaseOrderID)
GO

2. After creating a view and then creating a clustered index on the view, it is time to

check the effect of the index by running the query we have used in view definition
and also by running the view.
--Executing both the following queries with keeping Execution
--plan on we can turn execution plan on by pressing ctrl+M
--short cut key
SELECT TOP 10