Creando View con UNION y index

10/01/2008 - 21:28 por Oilers | Informe spam
Estoy creando una vista y tengo el siguiente problema

CREATE VIEW View4 WITH SCHEMABINDING
AS
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
union all
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice = ZZZ
GROUP BY ProductName, od.ProductID

GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4 (ProductName, ProductID)

el error es el siguiente:

Msg 10116, Level 16, State 1, Line 4

Cannot create index on view
'CDW_DM_STAR_APORI.dbo.dim_ReportProvider_ixv' because it contains one
or more UNION, INTERSECT, or EXCEPT operators. Consider creating a
separate indexed view for each query that is an input to the UNION,
INTERSECT, or EXCEPT operators of the original view.
 

Leer las respuestas

#1 Maxi Accotto
11/01/2008 - 01:30 | Informe spam
Hola, usted ha leido en sus libros sobre vistas indexadas y sus
limitaciones? si accede a ellos vera que no se permiten ese tipo de
sentencias como esta usted intentando hacer


Microsoft MVP SQLServer
www.sqltotalconsulting.com
-

"Oilers" escribió en el mensaje de
noticias:
Estoy creando una vista y tengo el siguiente problema

CREATE VIEW View4 WITH SCHEMABINDING
AS
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
union all
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice = ZZZ
GROUP BY ProductName, od.ProductID

GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4 (ProductName, ProductID)

el error es el siguiente:

Msg 10116, Level 16, State 1, Line 4

Cannot create index on view
'CDW_DM_STAR_APORI.dbo.dim_ReportProvider_ixv' because it contains one
or more UNION, INTERSECT, or EXCEPT operators. Consider creating a
separate indexed view for each query that is an input to the UNION,
INTERSECT, or EXCEPT operators of the original view.

Preguntas similares