I'm trying to use an indexed view but I found that it didn't work.
I used Northwind database. Here's the code:


SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,
QUOTED_IDENTIFIER,ANSI_NULLS ON
GO

CREATE VIEW Vdiscount2 WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice, SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice, SUM(UnitPrice*Quantity*Discount)AS SumDiscountPrice2, COUNT_BIG(*) AS Count, ProductID
FROM dbo.[Order Details]
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

After that I use execution plan and run this query

SELECT * FROM VDiscount2
WHERE sumPrice>10000

But the execution plan showed the [Order Details] was used instead of VDiscount2 view. How could this happened since the VDiscount2 view is physically stored in database.

Please give me some explanation about it?

upermadi,

Remember that the result set returned by the view has the same general form as a table and that is why the show plan shows the order details table. a view is just a virtual table.

jemacc,

But indexed view is phusically stored in database, isn't?
So the show plan must use the VDiscount2 view instead of order details table.

Actually, what I was trying is based on the example of article
"Improving Performance with SQL Server 2000 Indexed Views" from MSDN.

And it says that the query execution plan is used a Clustered Index Scan on the Vdiscount2 view.

Can you give me more explanation about it.
Thanks

use "Group By" outside the view. I mean to say that remove Group By Clause from View and use Group By while using the View.

Hope this helps


B. Anant