Lesson Learned: GUID as a Primary Key

In RDBMS, it’s very common to use an int value as a Primary Key (PK). In SQL Server, we have Identity column. In Oracle, we have Sequence.

I’ve never thought of using GUID as a PK. Why we need to spend extra 12 bytes for a PK? (int is 4 bytes vs. GUID is 16 bytes)

In my recent project, the system consists of 2 kind of databases. 1) Server-side RDBMS (SQL Server), 2) Mobile-side RDBMS (Android SQLite). Initially, we designed to use int datatype as a PK for tables which need to be synchronized between the 2 database. However, this approach cannot uniquely identify the record across the 2 databases at all time.

We researched and found a solution to change the PK to GUID. GUID = Globally Unique Identifier. The GUID is so random that the probability of the same number being generated randomly twice is negligible.

This solution saved us 1) Extra roundtrip to query the newly generated Identity value from server RDBMS, 2) Extra roundtrip to synchronize the Identity value from server to the mobile.

How to: Using GUID in Database

Database Data Type Generation
SQL Server UNIQUEIDENTIFIER NEWID()
Oracle RAW(16) SYS_GUID()

References:

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s