Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Red face Unanswered: General notion about Clustered Index

    Hi,
    Though I know Clustered index is done on the field which have the greater possibilities of repeatition of data,I often come across some sample projects where clustered index have been done in primary key. And most of the cases have the unique id as the primary key field.I think that is a wrong idea.Most of the people are using the clustered index in a wrong way.Bcoz Non clustered index is used to index the fields which have unique data. Plz suggest am I right or wrong.....
    Thanks!!
    Joydeep

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I suggest you are wrong. Clustered indexes, like any index, are most useful on high-selectivity columns. Reductio-ad-absurdum argument: a clustered index on a column that only held one value would be worthless.
    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 blindman
    I suggest you are wrong. Clustered indexes, like any index, are most useful on high-selectivity columns. Reductio-ad-absurdum argument: a clustered index on a column that only held one value would be worthless.
    Hi,
    I haven't got u.You mean to say " Clustered indexes, like any index, are most useful on high-selectivity columns."
    HIGH SELECTIVITY -WHICH MEANS COLUMN THAT HAVE FEW DUPLICATE VALUES.
    But I want to add here something -I was going through the book
    Mastering SQL SERVER 2000 by Mike Gunderloy & Joseph L.Jorden...
    Somewhere in the book in page 454 and page 458 tells just the opposite story of your comments.
    It tells -
    " Because of the way SQL SERVER uses clustered indexes to search for the ranges of data,clustered indexes are best created on columns with low selectivity.Low selectivity means that there are many duplicate values in the field."
    what about this ....
    Plz comment
    Joydeep

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I'll add a little fuel to the fire. Will your clustered or non-clustered index be used by the Optimizer? Here's some food for thought: http://www.sql-server-performance.co..._not_equal.asp

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Jan 2006
    Posts
    27
    Rudra, there are many things to keep in mind when shoosing indexes.

    1) Clustered indexes store data in the physical order. This reduces the number of blocks the index is stored on, and makes for a faster index. It will cause page splits and data moves if the values in the index go beyond the fill factor.

    2) Clustered indexes therefore spread hotspots amongst pages of a table, if using non-consecutive key values. which can actually improve insert performance and reduce contention for pages.

    Consecutive values for Clustered key make the last page the ONLY place a record can be added.

    3) Any index is always faster, but only if the selectivity is high. If you make the PK (or most common query index) clustered, and of HIGH selectivity, you'll show the fastest query speed.

    Balancing stuff like this is why DBA's exist. There isn't a rule which covers all cases. A high read table should have the most important index clustered. A High Write table needs to balance hotspots vs index maintainence overhead.

    Jay Grubb
    Technical Consultant
    OpenLink Software
    Web: http://www.openlinksw.com:
    Product Weblogs:
    Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
    UDA: http://www.openlinksw.com/weblogs/uda
    Universal Data Access & Virtual Database Technology Providers

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Thanks a lot to all of you.
    Its really a great stuff that gathered here.
    Now I think there should be no fuss about Indexing anymore.You guys did a great job making it much clearer...thanks again.
    And ofcourse, a word of thanks to John Grubb for those wonderful comments.
    Thanks !!
    Joydeep

Posting Permissions

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