|
Subject:
|
Need Help : Indexed View
|
|
Posted By:
|
upermadi
|
Post Date:
|
9/27/2004 7:58:59 AM
|
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?
|
|
Reply By:
|
jemacc
|
Reply Date:
|
9/27/2004 1:59:45 PM
|
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.
|
|
Reply By:
|
upermadi
|
Reply Date:
|
9/28/2004 12:32:52 AM
|
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
|
|
Reply By:
|
Anantsharma
|
Reply Date:
|
9/29/2004 6:51:03 AM
|
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
|
