Saturday, December 31, 2011

Problem with Primary key as an Integer

We all know that any good database design has a unique primary key. Now point is how to decide, whether our primary key should be integer or not ? Well, primary key as an Integer works well with local systems and easy to use and also works great while writing manual SQL statements. But what if one is not working on local system and working in a distributed environment where one has to deal with replication scenarios. In such scenarios, Integers can't be primary key as they have state (sequencing) and can become a major security threat. Now here system demand for something unique apart from integers. And here comes GUID into picture, which provides globally unique id. You might be thinking that, is it a good choice to use 16 bytes of primary key instead of 4 bytes, then my answer will be definitely YES only when sync'ing is required. Using GUID as a row identity feels more truely unique and databse guru Joe seems to agree. But again performance issues arises in various scenarios. Just wait a while for my next post on more performance impacts

Advantages of using GUID:

  • Unique across every server, every database, every table 
  • GUID's can be generated from anywhere, without doing round trip to database
  • Provides easy merging and distribution of databases among multiple servers