PK vs Clustered Index

  • tfifield

    SSCrazy Eights

    Points: 9655

    I'm wondering about the actual sort order and index/table fragmentation on a particular table. It has a GUID primary key, but no clustered index. The GUID was set up so that replication from mobile units can upload to the server. There are about 200 of these mobile units and they replicate about every 15 minutes.

    Does anyone know the effect this has on the server database?

    I've actually never seen a table with a PK but no clustered index before.

    Todd Fifield

  • GSquared

    SSC Guru

    Points: 260824

    A clustered index on a GUID is generally a bad idea, because it forces table fragmentation. Clustered indexes on tables with lots of inserts should generally be something sequential, like an ID or a timestamp.

    If the table has no clustered index, it also has no base sort order, and fragmentation is pretty much meaningless. If it's not hurting the performance on the database, don't worry about 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

  • tfifield

    SSCrazy Eights

    Points: 9655

    Thanks for the quick reply.

    Is there a way of adding an Identity to the table schema that is not for replication?

    Todd Fifield

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    This should get you started.

    http://msdn.microsoft.com/en-us/library/ms152543.aspx

    (that's the 2008 version of the article - but the 2005 is a direct link at the top).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • tfifield

    SSCrazy Eights

    Points: 9655

    Matt,

    Thanks for the pointer. I'll have a look and see if it makes any sense to add some sort of identity as a clustered index. With over 200 mobile unit subscribers and more being added all the time, it looks like it would be a real pain to set up.

    Todd Fifield

Viewing 5 posts - 1 through 5 (of 5 total)

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