Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Indexing non-unique data

    I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

    The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

    Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by lstacks
    I have two tables which are related. The first table(A) has a sequentially assigned unique key (primary) that has a cluster index built on it. This table has roughly 1,000,000 rows of data and grows daily.

    The second table(B) has a sequentially assigned unique key (primary). There is a column in table(B) which contains table(A)'s unique key. For each row in the table(A) there are roughly 30 rows in table(B).

    Should I build a clustered index on the table(B) column which contains the key to table(A) or a non-clustered index?
    I think custured index should do the trick,its my opinion...I feel clustered index are best for low selectiviy columns,i.e. the column which have many duplicates values. But see what the gurus suggest...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by blindman
    You can have only one clustered index on a table, though you can have many non-clustered indexes. Since you may have many foreign keys in a table you can't make all these lookups clustered, so generally non-clustered indexes are applied to foreign keys.
    But can't we make the foreign key column clustered index? I mean making the unique key not a clustered index...only a unique key column
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Istaks

    Welcome to the forum

    Not a guru but some musings:

    Well - a clustered index determines the physical order storage of data. So - it is useful if placed on an incrementing field as far as insertion of data is concerned as there will be no page splits based on insertion. It is also useful if you are likely to use >, < or between comparisons in a where condition on the clustered field.

    The former is not the case. Inequality operators are rarely used on identities so Id go with no too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2006
    Posts
    2
    So on table(B) create a clustered index on the unique key and a non-clustered index on the foreign key?

    All selections from this table will be based on the foreign key.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If ALL selects on this table will reference the foreign key and you will not be searching for individual records, then you would get a performance boost from using a clustered index on your foreign key.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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