PK vs Clustered Index

Question

PK vs Clustered Index

tfifield

SSCrazy Eights

Points: 9655
More actions
January 14, 2009 at 9:51 am

#126608

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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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 More actions · Answer 2

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: 124210 More actions · Answer 3

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 More actions · Answer 4

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