Advantages:
- Can generate them offline.
- Makes replication trivial (as opposed to int's, which makes it REALLY hard)
- ORM's usually like them
- Unique across applications. So We can use the PK's from our CMS (guid) in our app (also guid) and know we are NEVER going to get a clash.
Disadvantages:
- Larger space use, but space is cheap(er)
- Can't order by ID to get the insert order.
- Can look ugly in a URL, but really, WTF are you doing putting a REAL DB key in a URL!? (This point disputed in comments below)
- Harder to do manual debugging, but not that hard.
Personally, I use them for most PK's in any system of a decent size, but I got "trained" on a system which was replicated all over the place, so we HAD to have them. YMMV.
I think the duplicate data thing is rubbish - you can get duplicate data however you do it. Surrogate keys are usually frowned upon where ever I've been working. We DO use the WordPress-like system though:
- unique ID for the row (GUID/whatever). Never visible to the user.
- public ID is generated ONCE from some field (e.g. the title - make it the-title-of-the-article)
UPDATE:
So this one gets +1'ed a lot, and I thought I should point out a big downside of GUID PK's: Clustered Indexes.
If you have a lot of records, and a clustered index on a GUID, your insert performance will SUCK, as you get inserts in random places in the list of items (thats the point), not at the end (which is quick)
So if you need insert performance, maybe use a auto-inc INT, and generate a GUID if you want to share it with someone else (ie, show it to a user in a URL)
The simple answer is: **no difference, they are the same thing.
2020-08-20 Update: While GUIDs (as used by Microsoft) and UUIDs (as defined by RFC4122) look similar and serve similar purposes, there are subtle-but-occasionally-important differences. Specifically, some Microsoft GUID docs allow GUIDs to contain any hex digit in any position, while RFC4122 requires certain values for the version and variant fields. Also, [per that same link], GUIDs should be all-upper case, whereas UUIDs should be "output as lower case characters and are case insensitive on input". This can lead to incompatibilities between code libraries (such as this).
(Original answer follows)
Treat them as a 16 byte (128 bits) value that is used as a unique value. In Microsoft-speak they are called GUIDs, but call them UUIDs when not using Microsoft-speak.
Even the authors of the UUID specification and Microsoft claim they are synonyms:
From the introduction to IETF RFC 4122 "A Universally Unique IDentifier (UUID) URN Namespace": "a Uniform Resource Name namespace for UUIDs (Universally Unique IDentifier), also known as GUIDs (Globally Unique IDentifier)."
From the ITU-T Recommendation X.667, ISO/IEC 9834-8:2004 International Standard: "UUIDs are also known as Globally Unique Identifiers (GUIDs), but this term is not used in this Recommendation."
And Microsoft even claims a GUID is specified by the UUID RFC: "In Microsoft Windows programming and in Windows operating systems, a globally unique identifier (GUID), as specified in [RFC4122], is ... The term universally unique identifier (UUID) is sometimes used in Windows protocol specifications as a synonym for GUID."
But the correct answer depends on what the question means when it says "UUID"...
The first part depends on what the asker is thinking when they are saying "UUID".
Microsoft's claim implies that all UUIDs are GUIDs. But are all GUIDs real UUIDs? That is, is the set of all UUIDs just a proper subset of the set of all GUIDs, or is it the exact same set?
Looking at the details of the RFC 4122, there are four different "variants" of UUIDs. This is mostly because such 16 byte identifiers were in use before those specifications were brought together in the creation of a UUID specification. From section 4.1.1 of RFC 4122, the four variants of UUID are:
- Reserved, Network Computing System backward compatibility
- The variant specified in RFC 4122 (of which there are five sub-variants, which are called "versions")
- Reserved, Microsoft Corporation backward compatibility
- Reserved for future definition.
According to RFC 4122, all UUID variants are "real UUIDs", then all GUIDs are real UUIDs. To the literal question "is there any difference between GUID and UUID" the answer is definitely no for RFC 4122 UUIDs: no difference (but subject to the second part below).
But not all GUIDs are variant 2 UUIDs (e.g. Microsoft COM has GUIDs which are variant 3 UUIDs). If the question was "is there any difference between GUID and variant 2 UUIDs", then the answer would be yes -- they can be different. Someone asking the question probably doesn't know about variants and they might be only thinking of variant 2 UUIDs when they say the word "UUID" (e.g. they vaguely know of the MAC address+time and the random number algorithms forms of UUID, which are both versions of variant 2). In which case, the answer is yes different.
So the answer, in part, depends on what the person asking is thinking when they say the word "UUID". Do they mean variant 2 UUID (because that is the only variant they are aware of) or all UUIDs?
The second part depends on which specification being used as the definition of UUID.
If you think that was confusing, read the ITU-T X.667 ISO/IEC 9834-8:2004 which is supposed to be aligned and fully technically compatible with RFC 4122. It has an extra sentence in Clause 11.2 that says, "All UUIDs conforming to this Recommendation | International Standard shall have variant bits with bit 7 of octet 7 set to 1 and bit 6 of octet 7 set to 0". Which means that only variant 2 UUID conform to that Standard (those two bit values mean variant 2). If that is true, then not all GUIDs are conforming ITU-T/ISO/IEC UUIDs, because conformant ITU-T/ISO/IEC UUIDs can only be variant 2 values.
Therefore, the real answer also depends on which specification of UUID the question is asking about. Assuming we are clearly talking about all UUIDs and not just variant 2 UUIDs: there is no difference between GUID and IETF's UUIDs, but yes difference between GUID and conforming ITU-T/ISO/IEC's UUIDs!
Binary encodings could differ
When encoded in binary (as opposed to the human-readable text format), the GUID may be stored in a structure with four different fields as follows. This format differs from the [UUID standard] 8 only in the byte order of the first 3 fields.
Bits Bytes Name Endianness Endianness
(GUID) RFC 4122
32 4 Data1 Native Big
16 2 Data2 Native Big
16 2 Data3 Native Big
64 8 Data4 Big Big
Best Answer
I always tend to use surrogate primary keys in my database. That is: those primary keys have no actual meaning in the problem domain, and thus, those primary keys are never exposed to users. (If this surrogate primary key is of type GUID or an identity, I don't care; this depends on the requirements).
If you say that users should be able to identify objects based on a user-friendly ID, then, I think that this user-friendly ID is a value that belongs to your 'problem domain'. This means, that this ID should indeed be an attribute in your table, but it should not be used as the primary key in your table.
This also allows you to easily modify the value of such an user-friendly ID (if that should be necessary), without you having to worry about modifying related foreign keys as well.