It seems like there is an unending debate about using GUIDs in a database as key values versus the "always use integer" purists. The arguments to me are useless in most cases - just use some common sense and decide based on what works best to solve your particular business problem. Just so this is a more interesting post, I'll elaborate...
The naysayers, "GUIDs are 4x bigger than int columns, their values are too hard to remember, and they make the database too slow." Yes, GUIDs are bigger than ints. But does anyone complain about multi-column indexes that are 16+ bytes (GUIDs are 16 bytes)? Probably not, so rethink this argument. That being said, I personally don't following either the "GUIDs are always great" or "ints are always to the way to go" for primary key arguments. If you can think-through, and validate your reason for using one or the other, then go for it IMO. For example, one nice thing about GUIDs from a .NET application perspective is that you can create new records in the app without having to do an insert and then retrieve the primary key back as you would an int column - assuming you are using an identity column. But will the GUID be unique? Well, it's not 100% guaranteed; you could get a duplicate...and you could win the powerball lottery too so go buy plenty of tickets, right? :-) OK, that was a little sarcastic and probably a better analogy would be, “if you buy a network card, it might have the same MAC address as another network card” and I'm not too worried about that. So my point is - use what makes sense for your design and don't be afraid of GUIDs. Likewise, don't just use GUIDs for the sake of using GUIDs.
As for the performance argument, I've only seen problems where people have used clustered indexes on their GUID columns. Of course it is ridiculous to do this because a clustered index determines the physical ordering of the data. Clustered indexes are better used for values have some kind of range or ordering properties to them such as datetime and in some cases integers. So my point is - don't use clustered indexes on GUID columns. If you want to index a GUID column, use a nonclustered index. If you aren't sure, try out the Index Tuning Wizard - it's a pretty slick little utility for SQL Server.
GUIDs are hard to remember. But I would ask you, why are you trying to remember PK values anyway? If you are using either uniqueidentifier or identity columns then I assert that in the majority of cases memorizing PKs is poor practice. Other than that, for something like a lookup list I can see why an int would be nice if the PK remains constant. Furthermore, remember that for replication GUIDs are going to buy you a heck of a lot more than int identity columns. I've noted this to be true for Oracle and Access integration as well.
Thoughts?
