Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: 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 ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Ensure that there are no duplicates in the specified column.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    No duplicates ????Can a clustered index only be unique ??? What if I do not have unique values in the table ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Any Ideas Gurus ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't know...started a new one....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Index problem and another question

    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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])
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  10. #10
    Join Date
    Sep 2003
    Posts
    364
    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.

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    http://support.microsoft.com/default...b;en-us;293177


    Here is the solution to the problem ....

    I was hoping and I was right .....

    IT IS A DOCUMENTED BUG
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Mar 2004
    Location
    Germany
    Posts
    11
    Please post this also to SQLTeam.com for completeness

  13. #13
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809

    Re: Index problem and another question

    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?
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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