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 ?

Index of length xx exceeds the maximum length

Hi,
my program inserts data into a table and it's throwing an exception (below).
I'm not a SQL Server bod so can anyone tell me if there is an alter statemen
t
to increase the size allocated to the index or otherwise what I should do.
The index entry of length 1235 bytes for the index 'ixPDM_ActionDescription'
exceeds the maximum length of 900 bytes.Nope, 900 is max, maybe you should look at Full Text Search if you need
bigger sizes than 900 bytes
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||The maximum length of any index is 900 bytes per row. There's no way around
that. When I say "no way", I'm not entirely truthful - in SQL 2005
'nonessential' columns (i.e. not part of a constraint enforced by the index)
that would cause the index to be larger than 900 bytes (and thus preventing
data from being inserted) can still be included in the index.
Look up INCLUDE in Books Online.
In SQL 2000 you'd have to re-think the fact that you need indexes on columns
larger than 900 bytes.
What version are you using? What's the table DDL? What's the business
requirement? Why do these large columns need to be indexed?
ML
http://milambda.blogspot.com/|||Thank you for your replies. I think I might just drop the index as to my
knowledge I don't think the field is used in a join anywhere just the creato
r
of the db went a bit over the top with indexing.
"ML" wrote:

> The maximum length of any index is 900 bytes per row. There's no way aroun
d
> that. When I say "no way", I'm not entirely truthful - in SQL 2005
> 'nonessential' columns (i.e. not part of a constraint enforced by the inde
x)
> that would cause the index to be larger than 900 bytes (and thus preventin
g
> data from being inserted) can still be included in the index.
> Look up INCLUDE in Books Online.
> In SQL 2000 you'd have to re-think the fact that you need indexes on colum
ns
> larger than 900 bytes.
> What version are you using? What's the table DDL? What's the business
> requirement? Why do these large columns need to be indexed?
>
> ML
> --
> http://milambda.blogspot.com/