Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    55

    Unanswered: Two indexes on the same column!!!

    Definitely creating two indexes on the same table on the same field is a wrong decision! Do any of you have another opinion?
    Can any one provide me with any article that mentions the disadvantages of such thing?

    Regards,

  2. #2
    Join Date
    Sep 2003
    Posts
    39
    There is no real reason why you wouldn't have two indices on the same column.

    I usually have a primary key non-clustered on an Identity field and then incorporate the same field in a clustered index for speed purposes.

  3. #3
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    I actually was wondering about something regarding this recently.

    Sometimes you may want to create an index as part of a column (say the first 10 characters). Would it then be an idea to create both the partial index and the full index, and use them in any query where appropriate?

    Matt.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    May refer to this Performance Q&A link that may help in anyway.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are going to create a clustered index using the same field that is already indexed as the primary key, don't make that field the first column of your clustered index, otherwise I can't see how you get a lot of advantage from it. If you make it a secondary column, then at least the optimizer has a choice of two index orders for sorting and searching.

    blindman

  6. #6
    Join Date
    Jul 2003
    Posts
    55
    Do you think that this will increase the possibility of deadlocks? I mean having more than one index on the same column for more than one table in the DB,,,

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. Why would it?

    blindman

Posting Permissions

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