What are the benefits and detriments of having a GUIDs within your database? Can you rely upon them to be truly unique? Are there any hidden “gotcha’s” associated with GUIDs? Today, lets dig into that and find out.

The other day, I was talking to a friend of mine and we were discussing GUIDs, and his observation was that they were messy to use as Primary Keys and also thought that they had some serious disadvantages, such as being very slow in large numbers. I, being the everything must have some benefit and detriment, pointed out some of the good things about them such as their extreme uniqueness, but I wasn’t to positive about their performance, although I did agree that they were very messy for trying visually see how things were linked and also could be a huge waste of physical space. Being the curious person that I am, I decided that I would dig into this and try to figure out exactly what a GUID was, a benefit or a curse. Now for this article, I am going to reference a lot of other websites, so it might be fast and loose and for that, and I will apologize for that in advance.

What is a GUID ({3cecbfbb-b53e-42e6-ae85-8b543f4b1ac3})?

Now the first question is, what is a GUID? A GUID, according to The Quick Guide to GUIDs, is a 10³⁹ long hexadecimal number that is broken down into five sections (broken out in hex as 8, 4, 4, 4 and 12), and is supposed to be random with hints about how, when and where it was made. For me, that doesn’t cut it, so I dug further in and found a post by the Raymond Chen over at Microsoft’s MSDN are where he posted GUIDs are globally unique, but substrings of GUIDs aren’t, and describes how they are built.

60 bits of timestamp
48 bits of computer identifier
14 bits of uniquifier
06 bits are fixed

He goes onto describe how if a computer is generating two or more GUIDs within rapid succession (i.e. within the same second) or someone’s computer is back/forward date then the GUID counter will not be in the same place allowing the 14 bits of “uniquifier” to be different. To me, this means that the only way to get two GUIDS the same is if the records were 1) created at the exact same second, 2) the computer identifier has to equate out to be the same, 3) the GUID Counter must be in the same exact location on both machines.

OK, being at the exact same second is a difficult but not impossible thing for computers to create a record, but what about the hash of the computer identifier, how is that made? According to RFC 4122 Section 4.1.6, this is supposed to be generated from the IEEE 802 MAC Address, and if the system doesn’t have a MAC Address a pseudo-random value can be used. So in order to handle this, the computer must have either a) no MAC address and the pseudo-random number generator had to duplicate the values or b) you have to have installed a network card that had a hacked MAC address that is spoofing another computer (which would not allow you to be on the same network as the spoofed computer) or c) the same exact network card as another computer.

Lastly, the uniquifier, how does that work? Well, according to Ken Simmons who wrote Understanding and Examining the Uniquifier in SQL Server, the uniquifier is only hit when the record will be duplicated and the field is marked as Unique. This seems to be a safe bet the the GUID will be unique across different machines and times, even if the times are the same you should be pretty safe as long as the generating computer isn’t a virtualized computer, sharing physical access to the network card, running to a different SQL Server that is creating records at the same exact time and the same exact information.

Performance?

To test this out, I decided to build a program that would insert any number of records I wanted into one of three different table sets, a standard Integer controlled environment, using an integer key generated by the application, a GUID Automatically entered controlled environment and a GUID that the application would generate and manage. Very simple tables, almost identical except for the primary keys and the SQL Statements are as such

CREATE TABLE dbo.SalesOrdersInt(
	SalesOrderID INT NOT NULL,
	Description VARCHAR(50) NOT NULL,
	CONSTRAINT PK_SalesOrdersInt PRIMARY KEY CLUSTERED (SalesOrderID ASC)
)

CREATE TABLE dbo.SalesOrdersInt(
	SalesOrderID INT NOT NULL,
	SalesOrderDetailID INT NOT NULL,
	Description VARCHAR(50) NOT NULL,
	CONSTRAINT PK_SalesOrdersInt PRIMARY KEY CLUSTERED (SalesOrderID ASC, SalesOrderDetailID ASC)
)

CREATE TABLE dbo.SalesOrdersInt(

SalesOrderID INT NOT NULL,