I'm fairly well versed in SQL server performace but I constanly have to argue down the idea that GUIDs should be used as the default type for Clusterd Primary Keys.
Assuming that the table has a fairly low amount of inserts per day (5000 +/- rows / day), what kind of performace issues could we run into? How will page splits affect our seek performance? How often should I reindex (or should I defrag)? What should I set the fill factors to (100, 90, 80, ect)?
What if I were inserting 1,000,000 rows per day?
I apologize beforhand for all of the questions, but i'm looking to get some backup for not using GUIDs as our default for PKs. I am however completely open to having my mind changed by the overwehlming knowledge from the StackOverflow user base.
If you are doing any kind of volume, GUIDs are extremely bad as a PK bad unless you use sequential GUIDs, for the exact reasons you describe. Page fragmentation is severe:
And as this comparison between GUIDs and integers shows:
If your volume is very low, however, it just doesn't matter that much.
If you do really need a globally unique ID but have high volume (and can't use sequential IDs), just put the GUIDs in an indexed column.