Tuesday, April 12, 2011

GUID or INT for my Primary key field


When my Tech Lead came to me couple of months ago and asked me to do a particular SQL Database task and the task was to create a sample data table and create a .net application to to the CURD operations on it. 

At that time I was a bit nervous, whether to use INT data type as the primary key or to use GUID type as the primary key; so I started working on that and took a chance to speak to my other team members and do some kind of a research on it. after having a proper understanding on that I used INT as my primary key based on the details I found on technical articles.

Below are the main advantages and disadvantages about both INT and GUID data type.

Advantages

INT 
  1. Small amount of storage size (and integer is 4 bytes)
  2. Increased readability, practical use in testing, and easy to understand
  3. Support for functions that return the last primary key generated (@@IDENTITY, SCOPE_IDENTITY())

GUID
  1. The primary key is uniquely identified in the entire system (Unique across the server.)
  2. Easy to merge tables
  3. Don't have problems when inserting a large number of operations

Disadvantages

INT 
  1. Hard to work with distributed tables
  2. Primary key in the form of INT/BIGINT is LUID, local unique identifier, which is only used locally in a table
  3. After a large number of operations (insert, delete), the counter for primary key can be reset, bringing the problem of chronology to 1

GUID

  1. Bigger storage size (16 bytes - four times the size of an integer)
  2. Hard to remember and understand

No comments:

Post a Comment