SQL Server Performance

Error When I creating an Index on a view

Discussion in 'T-SQL Performance Tuning for Developers' started by balaatworkk@yahoo.com, Dec 15, 2006.

  1. 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
    db:confused:bject 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
  2. ranjitjain New Member

  3. madhuottapalam New Member

    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
  4. 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

Share This Page