RE:crear indice sobre una vista
Publicado por
pablo (1 intervención) el 26/04/2006 23:48:18
mira este es un còdigo de ejemplo extraido de las ayudas de sql server sino puedes pues vuelve a postear y ahi veremos como lo solucionamos
Create an index on a view
This example will create a view and an index on that view. Then, two queries are included using the indexed view.
USE Northwind
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON
GO
--Create view.
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID
GROUP BY OrderDate, ProductID
GO
--Create index on the view.
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (OrderDate, ProductID)
GO
--This query will use the above indexed view.
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev, OrderDate, ProductID
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND ProductID in (2, 4, 25, 13, 7, 89, 22, 34)
AND OrderDate >= '05/01/1998'
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC
--This query will use the above indexed view.
SELECT OrderDate, SUM(UnitPrice*Quantity*(1.00-Discount)) AS Rev
FROM dbo.[Order Details] od, dbo.Orders o
WHERE od.OrderID=o.OrderID AND DATEPART(mm,OrderDate)= 3
AND DATEPART(yy,OrderDate) = 1998
GROUP BY OrderDate
ORDER BY OrderDate ASC