RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Use Globally Unique Identifiers in Access and SQL Server

When your business outgrows your database systems, using globally unique identifier (GUID) values in Access and SQL Server can offer a timely solution.

Globally unique identifiers (GUIDs) distinguish hardware, software, and users. Originally, Microsoft designed the GUID value for use with ActiveX controls; Windows uses a 128-bit GUID to identify ActiveX controls. A 48-bit version generates an Ethernet adapter address. Because no two systems should have the same Ethernet adapter address, a GUID based on your computer shouldn't be duplicated on any other computer. To assure this precaution, a GUID datatype produces unique values using the time and address of your network card to ensure that no other computer can generate the same values.

These values, which are very long, consist of both numbers and letters. SQL Server and Access provide a 16-byte GUID datatype, a hexadecimal number with a guarantee of uniqueness for the next century. (Presumably, by then the duplicate value problem will be solved for the next millennium.) This article focuses on the nature and use of the GUID datatype in Access and SQL Server.

You'll Outgrow Your System—Sooner or Later
The availability of the GUID datatype brings to mind the ancient Chinese curse: "May you live in interesting times." Suppose you have at least one table that could store more than two billion rows. For instance, seismologists depend on a thousand different instruments buried in the ground to measure seismic activity every five seconds. As you can imagine, a table recording these measurements grows rather quickly. In fact, it isn't all that unusual for a seismologist's database to grow an average of 100MB per month.

You also can outgrow your system because primary key values are susceptible to the same recent trends affecting the marketplace, such as acquisitions, downsizing, and consolidations. Increasingly, information technology departments are expected to marry databases that different companies designed for different purposes. Suddenly you could find yourself having to merge varying databases—which all use an auto-incrementing datatype for their primary keys. As a result, all your tables have primary key values that increment by one and begin with the value of one. You're faced with a multitude of duplicate primary key values.

GUID primary keys could solve your problem, but at an intolerable performance cost. Don't rush out and convert all primary key fields to the GUID datatype. For the most part, system-generated key values need to be unique only to a single server. In most situations, changing an auto-incrementing primary to a GUID has no advantage, only the following disadvantages:

  • GUID values are up to four times larger than the alternatives (Access' AutoNumber and SQL Server's Identity columns are Long Integer datatypes), so GUIDs require more overhead.
  • GUID values take longer to search and sort. Consequently, a GUID primary key suffers a performance penalty that cascades throughout the database.
  • The number of characters in and the awkward composition of the actual GUID values themselves make them hard to remember or work with directly, although neither is really necessary when used as a primary key value.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date