Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software to demonstrate how to create, index and apply an indexed view in Microsoft SQL Server 2005:

USE AdventureWorks;

GO

-- drop view vidxDiscountSummary

CREATE VIEW vidxDiscountSummary

WITH SCHEMABINDING

AS

  SELECT   ProductName = p.Name,

           OrderTotal = SUM(UnitPrice * OrderQty),

           DiscountOrderTotal = SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)),

           OrderItems = COUNT_BIG(* ),

           ProductID = sod.ProductID

  FROM     Sales.SalesOrderDetail sod

           JOIN Production.Product p

             ON p.ProductID = sod.ProductID

  GROUP BY p.Name,

           sod.ProductID

GO

CREATE UNIQUE CLUSTERED INDEX vidxDiscountSummaryInd1 ON vidxDiscountSummary (

      ProductID)

CREATE UNIQUE INDEX vidxDiscountSummaryInd2 ON vidxDiscountSummary (

      ProductName)

CREATE INDEX vidxDiscountSummaryInd3 ON vidxDiscountSummary (

      OrderItems)

GO

SELECT   ProductName,

         OrderItems = left(convert(VARCHAR,convert(MONEY,OrderItems),1),

                           len(convert(VARCHAR,convert(MONEY,OrderItems),1)) - 3),

         OrderTotal = '$' + convert(VARCHAR,OrderTotal,1),

         DiscountOrderTotal = '$' + convert(VARCHAR,convert(MONEY,DiscountOrderTotal),

                                            1)

FROM     vidxDiscountSummary ds

ORDER BY ds.OrderItems DESC

GO