Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

( Guid = UniqueIdentifier ) type on SQL Server

share|improve this question
1  
I made a post ( goo.gl/cH0PZ ) comparing three types of ID. Thanks for the answer they were very helpful. –  BrunoLM Sep 13 '11 at 14:54
    
Rather than primary key, I think you mean surrogate key i.e. a key that is not the natural key (the latter being the key we use in the real world). Possibly you mean clustered index. –  onedaywhen Jan 31 '12 at 8:48
    
Also remember the difference between (Primary) KEY and INDEX. –  Allan S. Hansen Jan 7 at 6:55

3 Answers 3

up vote 38 down vote accepted

This has been asked in SO here and here

Jeff's this post will explain alot about pros and cos of using GUID.

GUID Pros

  • Unique across every table,every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

If you are certain about performance and you are not planning to replicate or merge records then use int, and set it auto increment (identity seed in SQL Server)

share|improve this answer
7  
Another con of the GUID approach is that you cannot use it as an identifier for your end-user. Do you really expect your users to tell you on the phone that they have an issue with Order "BAE7DF4-DDF-3RG-5TY3E3RF456AS10" ? :) –  Brann Jul 8 '11 at 21:22
    
If you do not use sequential guids, and your primary key is clustered (the SQL Server defaul) then all your data inserts will be randomly scattered throughout the table, leading to massive fragmentation of your data. That is assuming that the data would normally be inserted in some sort of order, such as chronological. –  datagod Aug 6 '11 at 4:32
2  
Sequential guids are only sequential until the SQL instance is restarted. Then the first value will more than likely be lower than the prior one because of the way that the root value is generated, causing all sorts of problems all over again. –  mrdenny Aug 6 '11 at 6:37

If you're synchronizing your data with an external source, a persistent GUID can be much better. A quick example of where we're using a GUIDs is a tool that is sent to the customer to crawl their network and do certain classes of auto-discovery, store the records found, and then all the customer records are integrated into a central database back on our end. If we used an integer, we would have 7,398 "1"s, and it'd be a lot harder to keep track of which "1" was which.

share|improve this answer

Some best practices out there still mention that you should use a data type that accomodates with the less memory possible the whole set of values you're going to use. For instance, if you're using it to store number of employers in a small business and you're unlikely to get to a 100, then no one would suggest in using a bigint value while int (even smallint) would do.

Of course, the drawback of this is like "Say no to scalability!"


Also, I know this is not totally related, but there's another factor regarding this. When not excesive, I usually try to recommend to use a non-autogenerated primary key, if it does make sense. For instance, if you're saving driver's information, don't bother in creating a new autogenerated column for "ID", just use the license number.

I know this sounds really obvious, but I see that being forgotten quite often.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.