create Index on view

Last Post 17 Dec 2006 07:37 PM by balaMunugoti. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
balaMunugoti
New Member
New Member

--
14 Dec 2006 09:30 PM

would like to create an index on one of my views and I keep getting an error:
"Cannot create index on view 'View1' because the view is not schema bound.". I wonder if someone has any ideas if you can find a way around this error or maybe there is an alternative way of improving performance of a given view.

you can see my view below

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

create 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 GraffitiCategory WHERE GraffitiCategory.Id = [Case].CategoryType) AS CategoryTypeName,
[Case].ThreadType AS ThreadType,
[Case].Threatening AS Threatening,
(SELECT Team.Name FROM 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 Team.TeamType = 4 and CaseTeam.CaseId = [Case].Id) AS CaseDepartment
FROM [Case] INNER JOIN
Object ON [Case].Id = Object.Id LEFT OUTER JOIN
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
Thanks!
SQLUSA
New Member
New Member

--
17 Dec 2006 01:02 AM
Check BOL.

There are some requirements to be met before you can create an index view.

Kalman Toth
URL: http://www.sqlusa.com
balaMunugoti
New Member
New Member

--
17 Dec 2006 07:37 PM
Thanks for your reply

Team,CaseTeam,Object and Address They are tables

Still I have this problem

When i create the index on a view its restrict some rules like outer joins and inner queries,If i remove that restrictions and change the view, we loose many records.
If you know the alternate solution or any modifications Please let me know
This is very urgent to me.
balaMunugoti
New Member
New Member

--
18 Dec 2006 07:35 PM
Thank you for your reply

I didn't understand the below statement,Can you explain in the better way..

The compatibility level of the database cannot be less than 80. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

Thank you.
You are not authorized to post a reply.

Acceptable Use Policy