What is Advantages/Disadvantages using GUID over Integer as primary key

No.of Views3043
Bookmarked0 times
Downloads 
Votes0
By  jalpesh   On  05 Dec 2010 20:12:14
Tag : Sql Servers , Database Administrator
In this article, I would list out Advantages and Disadvantages using GUID over Integer as primary key in Sql server. Because when you are creating table, you have to decide a correct data type for the primary key for better database performance.
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article, I would list out Advantages and Disadvantages using GUID over Integer as primary key in Sql server. Because when you are creating table, you have to decide a correct data type for the primary key for better database performance.Recently one of my friend ask me when I should go for GUID and When I should go for Int as primary key in table. So decided to write a simple article for it. Here are advantages and disadvantage of the GUID and INT.

INT Data Type

Advantages

1. Its required small space in terms of the storage it will only allocates 4 bytes to store data.
   2. Insert and update performance will be faster then the GUID. It will increase the performance of the application.
   3. Easy to index and Join will give best performance with the integer.
   4. Easy to understand and remember
   5. Support of function that will give last value generated like Scope_Indentity()

Disadvantages

 1. If you are going to merge table frequently then there may be a chance to duplicated primary key.
   2. Limited range of uniqueness if you are going to store lots of data then it may be chance to run out of storage for INT data type.
   3. Hard to work with distributed tables.

GUID Data Type

Advantages

   1. It is unique for the current domains. For primary key is uniquely identifies the table.
   2. Less chances of for duplication.
   3. Suitable for inserting and updating large amount of data.
   4. Easy for merging data across servers.

Disadvantages

   1. Bigger storage size (16bytes) will occupy more disk size then integer.
   2. Hard to remember and lower performance with Join then integer.
   3. Don’t have function to get last uniquely generated primary key.
   4. A GUID primary Key will added to all the other indexes on tables. So it will decrease the performance.

Conclusion

From above the advantages and disadvantages we can conclude that if you are having very large amount of data in table then go for the GUID as primary key in database. Otherwise INT will give best performance. Hope this will help you. Please post your opinion as comment.

 
Sign Up to vote for this article
 
About Author
 
jalpesh
Occupation-Software Engineer
Company-DotNetJaps
Member Type-Gold
Location-India
Joined date-08 May 2010
Home Page-http://www.dotnetjalps.com
Blog Page-http://www.dotnetjalps.com
I am jalpesh vadgamaa an Microsoft MVP for Visual C# and BrainBench Certified ASP.NET Developer having experience of five year in Microsoft .NET Technology.I am working as Project Leader in Mid Size company.My work area comprises of Enterprise Level projects using ASP.NET and other Microsoft .NET Technologies.Please feel free to contact me for any queries via posting comments on my blog I will try to reply as early as possible.
 
 
Other popularSectionarticles
Comments
By:KhushiDate Of Posted:7/21/2012 5:35:33 AM
helpful article
Awesome way of explaining...it helped me a lot..thanks!!
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</