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. :confused: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?|||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.|||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?|||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 ?