Tag: Oracle

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: