Index problem and another question

create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.


However I do not have any other indexes on the table. What could be the problem ?

And where did the dBForums Yak corral vanish ??Ensure that there are no duplicates in the specified column.|||No duplicates ??Can a clustered index only be unique ? What if I do not have unique values in the table ?|||Any Ideas Gurus ??|||I don't know...started a new one...|||Originally posted by Enigma
create clustered index AOP_C_Idx on AOP_Master([Date])

Running this query gives the error

Server: Msg 169, Level 15, State 2, Line 2
A column has been specified more than once in the order by list. Columns in the order by list must be unique.


However I do not have any other indexes on the table. What could be the problem ?

And where did the dBForums Yak corral vanish ??

Is that the exact sql?|||Originally posted by Enigma
No duplicates ??Can a clustered index only be unique ? What if I do not have unique values in the table ?

CREATE a non unique index?

What are you trying to do?|||I am trying to create a non unique Clustered index on a table which already exists and getting the error .

And yes this is the exact sql I am using

create clustered index AOP_C_Idx on AOP_Master([Date])|||What happens when you run

select *
from AOP_Master
order by [Date]?

Do you somehow have two date columns in the table? Can't be.|||I've had similiar strange unexplainable problems trying to create foreign keys and indexes too. I would try to create the fk or index and SQL server thought the index was already there eventhough it wasn't. I had to drop and re-create the table in order to get the fk or index to create. This is a shot in the dark but hope it helps.|||http://support.microsoft.com/default.aspx?scid=kb;en-us;293177

Here is the solution to the problem ...

I was hoping and I was right ....

IT IS A DOCUMENTED BUG|||Please post this also to SQLTeam.com for completeness|||Originally posted by Enigma
And where did the dBForums Yak corral vanish ??

I guess I have been away for a while... what is a sbForums Yak corral?

Index Creation Date

Does Microsoft SQL Server keep a record of an index creation date and
last modified date in a system table somewhere?If the index is a primary key or unique constraint, then you can find
the create data in sysobjects - see sysobjects in Books Online for the
details. Otherwise, I don't think it's possible. MSSQL 2000 doesn't
store the modified date for objects, although 2005 does.

Simon|||hi
you can check for crdate in sysobjects table

just try linking sysindexes and sysobjects tables in master database. u
might get the solution

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***