To GUID or not to GUID In Your Databases

This topic has come up in discussions quite a few times, with many database designers and developers seemingly believing that GUIDs are the way to go simply because they’re large and intimidating. While GUIDs can be the best choice, in many cases they’re simply wasteful extravagance.

GUID Basics

Let’s start with some definitions – GUID is a Globally Unique IDentifier, and it’s the Microsoft take on the OSF’s UUIDs (Universally Unique IDentifier. I guess Microsoft is more pessimistic than the Open Software Foundation). It is a 128-bit (16-byte)
pseudo-randomly generated value that, if the algorithm is correct, should theoretically never collide with another GUID generated in any other place at any other time (at least until the year A.D. 3400). You can generate GUIDs in T-SQL Using NEWID(), while in .NET you can use the System.Guid value type’s static method NewGuid(). In the Win32 world you can call CoCreateGUID().

Historically GUIDs used the network card’s MAC address as the starting point, incorporating a time component and then incrementing a value part sequentially with each generation, however privacy concerns abolished that standard (because GUIDs, such as those embedded in a Word document, could be associated with a specific network card – and thus a specific computer – presuming the MAC address wasn’t cloned. Note that you can still generate GUIDs in this old-fashioned way via UuidCreateSequential in rpcrt4.dll). Nowadays GUIDs are virtually random (there is a time component, but from the perspective of a user each value generation seems unrelated).

For instance I just generated the following 6 GUIDs in a row.

FD202BEE-05EC-42FF-A9DE-153C507CAC60
BA5C300C-61DC-4AFB-9DDF-2EDEFED533F2
57BEB108-80D3-40B9-8CFD-0406E544156C
3848803F-01DB-4D14-B1DF-AFBFB3A7544B
23BD5BD5-B2AA-496A-B365-24E02224369B
13A03BD6-9521-4C72-AFF9-121F941EF0DC

Not a very logical progression.

Globally Unique

In cases where you need global uniqueness, however, GUIDs are critical. There were (and still are) tens or hundreds of thousands of development shops throughout the world creating COM components for the Windows platform, with no central registry where they could register specific component names to ensure that no more than one of them was creating an Image.Processor component. As such, COM components were early-bound to GUIDs instead of names (late-binding still had the name conflict issue), so at yafla we
might tag our Image.Processor COM component with the generated GUID A2358A9A-C96D-4D72-B0E4-B732332408D6. It was very unlikely that another vendor would unintentionally collide with that value. This sort of global uniqueness, through the use of
GUIDs, has carried over to a lot of other technologies as well, including .NET.

GUIDs In Your Database

This same sort of global uniqueness can play a role in our databases as well. Primarily when we want to merge the contents of several databases or database sites, maintaining the relational integrity and without changing the keys, and where our primary keys
are surrogate keys rather than natural keys. In this case using a GUID as a primary key can allow for the relatively painless merging of datasets, and while the represented
data may include logical duplicates (e.g. Bob Jones might be in both the SuperHyperMart database and the HyperMegaMart databases when they merge), the relational integrity and source keying will remain. For planned distributed databases a GUID isn’t actually
necessary, though: If you have a sales computer logging sales in New York, and another in Tokyo, and nightly you merge these databases, you can avoid collisions by automatically or manually assigning ranges to each database (e.g. New York autonumbers starting at 0, while Tokyo numbers from 1,000,000,000 on). Merge processes can easily rebase keys where necessary as well, again eliminating the need for GUIDs, however the keys will differ from those on the source system.

The Cost of GUIDs

GUIDs don’t come for free. The algorithms used to create GUIDs are relatively intensive, for instance, and even then the global uniqueness is largely theoretical (I’m a cynical sort, and view the idea of an alogorithm that generates pseudo-random numbers “guaranteed” unique across space and time suspiciously). Due to this overhead, inserting into a database using generated GUIDs can be onerous, and far slower than inserting in a table using autonumbers. There are some webpages out there advocating techniques of creating sequential GUIDs, eliminating the onerous GUID creation cost, but then the point of using a GUID in the first place is lost (and it is then more accurately a 128-bit number, and there is no rational assurance of global uniqueness).

On top of that, GUIDs are data pigs, taking up 16-bytes each. While this sounds miniscule in an era of monster memory and collosal hard drives, when you’re dealing with enterprise databases with hundreds of tables with millions of rows each, such an overhead becomes extraordinary. Several adhoc benchmarks exist “proving” that the overhead of GUIDs has little impact compared to an int, however these comparisons almost always deal with query loads and datasets that entirely fit within the memory cache. The story would be vastly different dealing with a real, highly-relational enterprise system. In such systems it is the norm that the I/O system is the weak link (even with extremely expensive SAN systems), with the I/O pipe saturated continually. Unfortunately I don’t have the resources to setup a high performance enterprise SAN-backed system to demonstrate this
point, however I’ve dealt with some large enterprise systems where the storage I/O was overwhelmingly the weak point.

When used as primary keys that also serve as the clustered index, GUIDs can also lead to significant page splitting, as rows are constantly being inserted amidst the exist data. Compare this to an autonumber where in the same scenario each new record would be added to the end (historically that led to a hot spot of heavy contention, but all modern database systems deal with it very elegantly).

GUIDs – The Pros

  • Globally unique. Immediately usable in merge scenarios
  • Can be generated on the middle tier, allowing developers to build all relational rows before pushing it simultaneously to the data store. Compare this to an autonumber scenario where the root row often needs to be pushed to the database to generate an
    autonumber to be used by the related rows Very large data space – it’s unlikely that your database will run out of GUIDs
  • In some cases a GUID can provide a bit of security through obscurity – if you publish a special command executable only by passing a secret GUID, it would take an average of 1.7e+38 attempts to get the right value. Of course this is a pretty marginal advantage

GUIDs – The Cons

  • It’s processor/time intensive to create real GUIDs compared to autonumbers
  • GUIDs are data hogs, taking 4x the space of an int, and twice the space of a bigint. In a highly relational design this data bloat is amplified, impacting related tables and indexes as well
  • Pseudo-random GUIDs lead to significant data and index fragmentation
  • GUIDs can be unintuitive for developers – it’s easier to remember SurveyID 72 than it is SurveyID 11DF30D5-FAAF-4896-83D4-C781ACDBB899. Likewise GUIDs can
    be unintuitive for users – a URL with a GUID is much uglier than the same with an integer

Conclusion

Every situation is different, and there most certainly are appropriate times and places for GUIDs (a universally unique time and place!). Just don’t toss rational evaluation in the wind and adopt the GUID by default under the illusion that it’s any more “Enterprise” ready: In reality the opposite is often the case.