Ygthkb

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}

This has been asked in Stack Overflow here and here.

Jeff's post explains a lot about pros and cons of using GUID.

GUID Pros

• Unique across every table, every database and 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 Server 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).

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.

I have used a hybrid approach with success. Tables contain BOTH an auto-increment primary key integer id column AND a guid column. The guid can be used as needed to globally uniquely identify the row and id can be used for queries, sorting and human identification of the row.

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.

For context: this part of the answer was addressed from a data theoretical approach, where you want your PK to be the uniquely data-identifier for a record. Most of the times we create those when they already exist, hence the previous answer.

However, it is very rare that you can have tight control over these datapoints, and as such, you may need to make corrections or adjustments. You can't do that with primary keys (well, you can, but it can be a pain).

Thanks @VahiD for the clarifications.