Dumb Question!

  • I would like to know if there could be any possible advantage to having an identity primary key field set as non-clustered and having a separate clustered index on the same field? I really don't there believe there could be, but need to verify it. Also, would there be any disadvantages to maintaining this arrangement?

    Ron K.

    "Any fool can write code that a computer can understand. Good programmers write code that humans can understand." -- Martin Fowler

  • No advantages I can see, and the disadvantage is that you are maintaining 2 indexes where you only really need to maintain 1.

    Note, there are a few, very special, very rare cases where you may want this arrangement. They are not common. More likely, it's a mistake made by someone doing index tuning.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • These two concepts are not necessarily related at all.

    A PK defines uniqueness on your data rows. It allows you to find an individual row out of a group. Whether this is clustered or not, it's purpose is to provide a locator for that row.

    A clustered index orders the data in index order, and is best used for range type queries. It has nothing to do with uniqueness or keys.

    Clustered indexes can be affected by inserts/updates, causing page splits and internal fragmentation, so you want to be aware of this when choosing a clustered index.

    I've often had some ID (identity/GUID) as the PK, but used a date for a clustered index since I often query on date ranges.

  • Steve Jones - Editor (7/7/2008)


    I've often had some ID (identity/GUID) as the PK, but used a date for a clustered index since I often query on date ranges.

    Likewise, but Ron (if I read properly) has a clustered and a nonclustered index on the same field.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ron Kunce (7/7/2008)


    I would like to know if there could be any possible advantage to having an identity primary key field set as non-clustered and having a separate clustered index on the same field? I really don't there believe there could be, but need to verify it. Also, would there be any disadvantages to maintaining this arrangement?

    No advantage. Possible disadvantages (in terms of double-index writes, etc.).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • No advantage, disadvantage possibilites include an impact on performance due to the multiple indices.

  • Non-sequential inserts on a clustered key are a huge problem. It makes for page splits and some very slow inserts once a page split has occured. You need to size the FILL FACTOR correctly to keep page splits from happening and you need to maintain the indexes every night (or more) on tables have a large number of inserts in such a situation.

    I know I'll get a huge amount of flak for this, especially if the likes of Celko show up on this thread, but I always have the clustered index on something that maintains the order of inserts so that the inserts go to the logical "end" of the table, thereby preventing page splits do to inserts. That means, many times, that the clustered index will be on some sort of "created" date column or an ID column. It also means that I may have to have a non-clustered unique index on another key for the table. It doesn't really violate what a lot of people have to say about trying to use natural keys (although everything has exceptions) because the Clustered IDENTITY or date column is not necessarily exposed to the user. It's usually just to keep page splits from happening. I'll frequently call the IDENTITY column "RowNum" just to keep the zealots off my back but it really is an "ID" column no matter how you slice it.

    Ok... duck... here they come... 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • PORK CHOPS!!!!! (lol) :w00t:

    Couldn't resist (well, I could have, but didn't want too!)

    😎

  • Jeff, I do the same thing.

    Relational Theory is a wonderful thing, but every now and again (not more than ten or fifteen times per nanosecond), it runs into this little detail called "physical universe reality".

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (7/9/2008)


    "physical universe reality".

    Reality? What's that? 😉

    Jeff Moden (7/9/2008)


    I always have the clustered index on something that maintains the order of inserts so that the inserts go to the logical "end" of the table, thereby preventing page splits do to inserts.

    Likewise, especially if it's a fast changing table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Reality - What a Concept (-- Robin Williams)

    😎

  • hope someone can help me get a bit more educated with sql server.

    if i have a clustered index on an identity column and insert a row, what lock is taken? is it an exclusive lock on the last page? if this is the case and i do another insert, would the 2nd insert get blocked by the 1st one completing?

  • The answer is that it can end up blocking, depending on what else is going on and what you're doing with uncommitted transactions. The only time I've seen it be a problem was someone who specifically was holding open a long-running transaction on the insert, then trying to insert again while it was still open. Haven't ever had that problem in any of my databases, but I did see a guy run into it.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (7/9/2008)


    Jeff, I do the same thing.

    Relational Theory is a wonderful thing, but every now and again (not more than ten or fifteen times per nanosecond), it runs into this little detail called "physical universe reality".

    Cool... I deal with "physical universe reality" as heaped upon me by others less able 😉 Glad to see I'm not alone there... :w00t:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis (7/9/2008)


    PORK CHOPS!!!!! (lol) :w00t:

    Couldn't resist (well, I could have, but didn't want too!)

    😎

    Now THAT's entertainment! 😛 Haven't tried it yet, but I understand that Pork Chops go very well with "duck"... :hehe:

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply