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

    Unanswered: Non Clustered Index Question

    Hi,

    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
    Location
    221B Baker St.
    Posts
    486
    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
    Location
    In front of the computer
    Posts
    15,579
    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.

    -PatP
    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
  •