Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Clustered index on a non-primary key

    Let's say I have a table with 3 columns: ID (the primary key), "ThreadID", and "Message".

    I want the rows to be physically ordered together by ThreadID. This means all rows that have ThreadID = 1 are adjacent to each other, and all rows with ThreadID = 2 are adjacent to each other etc. I think this is called a clustered index.

    My question: What are the implications of the above scenario? That is, what are the implications of having a non-primary key as a clustered index. What sorts of problems occur?

    Thanks!
    Last edited by dbguyfh; 02-24-09 at 17:19.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, they may not be exactly adjacent to each other due to page splits, and other storage oddities, which is probably the biggest problem. If you are expecting a lot of insert activity all over the table (across many threads), then you will see a lot of page splits, and insert performance will suffer. Reading the pages back, you will likely get a lot of half filled pages (i.e. data fragmentation). which will eventually hurt read performance. Regular defragmentation should help the read performance, but may hurt the insert performance, until you hit the right fillfactor.

    As for how to implement it, it is fairly straightforward in BOL.
    Code:
    create clustered index [indexname] on [tablename] (ThreadID, Message)

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Thanks for the quick reply.

    No problem if they aren't exactly adjacent; I just didn't want other rows with ThreadID != X to fall in between a set of rows where ThreadID = X.

    Question about the implementation: You have, "...[tablename] (ThreadID, Message)"

    Why the ",Message" ? Why not just (ThreadID) ?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    P.S. the only way to guarentee the order of a resultset is to use ORDER BY
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ahh. I misread your original post, and thought you wanted the two columns on the clustered index. No order to these messages?

  6. #6
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by MCrowley
    Ahh. I misread your original post, and thought you wanted the two columns on the clustered index. No order to these messages?
    No, there need not be any ordering. My end goal is to randomly select a particular row of a particular thread ID.

    What are the implications of having a clustered index on something that's not the primary key? Are there any problems with it?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As before, the main problem is the page splitting. Clustered indexes are mainly intended for range searches. No reason they have to be the primary key.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    in fact if PK has no FK I will use my cix somewhere more useful but it depends on what the table is being used for.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Page splitting has nothing to do with whether a unique clustered index is on the primary key or not. Page splitting is caused if a row needs to be inserted or updated on a page that doesn't have space on it.

    That won't happen for INSERTs if the value in the unique clustered index always increases with each successive insert - for example if it's an IDENTITY column. What column(s) the PRIMARY KEY constraint is declared on makes no difference at all.

Posting Permissions

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