Showing posts with label GUID. Show all posts
Showing posts with label GUID. Show all posts

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