SQL Server Performance Forum – Threads Archive

Error When I creating an Index on a view

Hi
I have created view and when i creating the index for that view I got the below Error
You can see one of my view below ===============================
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON GO alter VIEW dbo.vwCases with schemabinding
AS
SELECT [Case].[Id] AS [Id], [Case].Created AS Created, [Case].SqFeets AS SqFeets, [Case].GraffitiType AS GraffitiClass,
(case
when [Case].GraffitiType = 0 then ‘None’
when [Case].GraffitiType = 1 then ‘Graffiti’
when [Case].GraffitiType = 2 then ‘Tag’
when [Case].GraffitiType = 4 then ‘Unknown’
when [Case].GraffitiType = 8 then ‘Not Defined’
when [Case].GraffitiType = 7 then ‘All Defined’
when [Case].GraffitiType = 16 then ‘Multiple Image’
else ‘Error’ end
) AS GraffitiClassName,
[Case].SurfaceType AS SurfaceType,
(case
when [Case].SurfaceType = 0 then ‘None’
when [Case].SurfaceType = 1 then ‘Bridge’
when [Case].SurfaceType = 2 then ‘Curb’
when [Case].SurfaceType = 4 then ‘Electric Box’
when [Case].SurfaceType = 8 then ‘Fence’
when [Case].SurfaceType = 16 then ‘Fire Hydrant’
when [Case].SurfaceType = 32 then ‘Garage Door’
when [Case].SurfaceType = 64 then ‘Mailbox’
when [Case].SurfaceType = 128 then ‘Pole’
when [Case].SurfaceType = 256 then ‘Sidewalk’
when [Case].SurfaceType = 512 then ‘Sign’
when [Case].SurfaceType = 1024 then ‘Signal’
when [Case].SurfaceType = 2048 then ‘Tree’
when [Case].SurfaceType = 4096 then ‘Wall’
when [Case].SurfaceType = 8192 then ‘Window’
when [Case].SurfaceType = 16384 then ‘Other’
else ‘Error’ end
) AS SurfaceTypeName,
[Case].PhotoDateTime AS PhotoDateTime, [Case].Code AS Code, [Case].GpsLatitude AS GpsLatitude,
[Case].GpsLongitude AS GpsLongitude, [Case].ProcessedDateTime AS ProcessedDateTime, [Case].[Case] AS [Case],
[Case].RemovedDateTime AS RemovedDateTime,
[Case].CategoryType AS CategoryType,
(SELECT Graffiticategory.Category FROM dbo.GraffitiCategory WHERE GraffitiCategory.Id = [Case].CategoryType) AS CategoryTypeName,
[Case].ThreadType AS ThreadType,
[Case].Threatening AS Threatening,
(SELECT Team.Name FROM dbo.Team WHERE Team.Id = [Case].Threatening ) AS ThreateningName,
[Case].GraffitiClass AS GraffitiType,
(case
when [Case].GraffitiClass = 0 then ‘Publicity’
when [Case].GraffitiClass = 1 then ‘Roll Call’
when [Case].GraffitiClass = 2 then ‘Threat’
when [Case].GraffitiClass = 4 then ‘Territorial’
when [Case].GraffitiClass = 8 then ‘Sympathetic’
when [Case].GraffitiClass = 16 then ‘Not Defined’
else ‘Error’ end
) AS GraffitiTypeName,
ISNULL([Case].AbandentCrew,” ) AS AbandentCrew,
ISNULL(Address.StreetAddress, ”) AS StreetAddress, Address.StreetAddress2 AS StreetAddress2, Address.Country AS Country,
Address.State AS State, Address.City AS City, Address.ZIP AS ZIP, Address.CrossStreet AS CrossStreet,
–(select Team.[Id] from Team inner join CaseTeam ON Team.[Id]=CaseTeam.GangId where CaseTeam.CaseId = [Case].Id and Team.TeamType = 4) AS CaseDepartment
(select Team.[Id] from dbo.Team inner join dbo.CaseTeam ON Team.[Id]=CaseTeam.GangId where Team.TeamType = 4 and CaseTeam.CaseId = [Case].Id) AS CaseDepartment FROM dbo.[Case] INNER JOIN
dbbject ON [Case].Id = Object.Id LEFT OUTER JOIN
dbo.Address ON [Case].AddressId = Address.Id
WHERE (Object.Deleted IS NULL) AND [Case].GraffitiType < 9 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
========================================
When I create the Index
CREATE UNIQUE CLUSTERED INDEX IND_vwcases ON vwcases([Id])
GO
I got this Error
"Cannot index the view ‘LiveGraffiti.dbo.vwCases’. It contains one or more disallowed constructs."
If you know the reason,Please reply back to me.
Thanking you BalaMunugoti
Equinox Global Technologies

---

Hi,
please go through this link to find out the requirements for indexed views. http://www.sqlteam.com/item.asp?ItemID=1015

---

Hi, There are many restriction when u use Indexed view. what i can point out quickly in your script is You can not use *Outer Join* in indexed views. Madhu

---

Thanks for your Reply I gone through the view now .Now I can understand the restrictions when creating an index on a view.I will try it out. If you have any alternate solutions for the modifications for above view .Please reply back to me Thanks and Regards

---

]]>