Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008

    Unanswered: Non Clustered Index Question


    In Microsoft SQL Management Studio 2005 I have the ability to add a single non clustered index on a table on multiple columns (ordered how I want) AND/OR I may create a multiple of these non-clustered Index entries with a single column per non-clustered index.

    Is there a difference between to two options? If yes, how do these options work differently? I assume option 1 is just a faster way of creating the non-clustered index and there is no architectural difference!?
    Last edited by Eric the Red; 11-11-12 at 12:55.

  2. #2
    Join Date
    Oct 2009
    221B Baker St.
    How you implement the indexes should depend on how they are to be used.

    "Archetecturally" they are quite different (if i understand the question). A compound index versus multiple individual indexes.

    Depending on usage, either or both might be appropriate.

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Each index holds the information about the rows in your table in a "pseudo-sorted" fashion. There is information in the B-Tree that describes the order of the rows according to the column or columns in the index expression.

    If you index each column individually, then the rows will be grouped by the column being indexed, and the order of any "ties" with the same value for the indexed column will be determined by the database engine.

    If you index a group of columns in a single index, then the rows will be represented ordered by the first (leftmost) column, with ties in that column ordered by the next column, and so on. This behavior may or may not be useful to you, but it often helps queries perform better if multiple columns appear in the WHERE clause and even more so when those columns appear in the same order within an ORDER BY clause.

    As papadi pointed out, we don't have enough information to give you a simple yes or no kind of answer, but maybe this will help.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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