Results 1 to 5 of 5

Thread: Index on View

  1. #1
    Join Date
    Jun 2004
    Posts
    8

    Unanswered: Index on View

    Hi

    I have a problem creating an index on a view. The view should return the record corresponding to the Maximum Obje_ID. This seems to work.

    CREATE VIEW dbo.D_Object_View
    WITH SCHEMABINDING
    AS
    SELECT
    Policy_ID,
    Obj_ID,
    Environment_Code,
    CoB,
    Sub_CoB,
    Policy_No,
    Version_No,
    Object_Type,
    Item_Seq,

    FROM dbo.D_Object
    WHERE
    (Obj_ID IN
    (SELECT MAX(Obj_ID)
    FROM dbo.d_object
    GROUP BY Environment_Code, COB, Policy_No, SUB_COB, Object_Type, Item_Seq))


    I create the index with the following statement :
    CREATE UNIQUE CLUSTERED INDEX [IX_Object_ID] ON [dbo].[D_Object_View]([Obj_ID]) ON [PRIMARY]

    but get the following error :
    Cannot index the view 'DB.dbo.D_Object_View'. It contains one or more disallowed constructs.

    I think it is because of the MAX statement but don't know of any other way to do it.

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Is Obj_ID part of an index in the parent tables? If so the index on the view may not buy you much performance improvement. How many rows are in each table and what's the execution plan look like for the view sql without the index?

    Have you tried creating a non-unique index on the column?

  3. #3
    Join Date
    Jun 2004
    Posts
    8
    Yes Obj_ID is an index on the parent table and it cpontains aprox. 5 mil records but will increase as i need to add more data.

    I have tried creating a non-unique one but get the following error :
    Nonunique clustered index cannot be created on view 'D_Object_View' because only unique clustered indexes are allowed.

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    Sorry, forgot about that I'm sure you've tried it but what about a non-clustered index? And does the optimizer utilize the existing index in the execution plan?

  5. #5
    Join Date
    Jun 2004
    Posts
    8
    A nonclustered gives me the following error:
    Cannot create index on view 'D_Object_View'. It does not have a unique clustered index.

    My knowledge of SQL is limited but if I understand correctly about the optimizer ... the estimated execution plan utilises a Index scan. This is good right ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •