SQL Server guid vs INT- Which is better as a primary key?
I've been reading around reasons to use or not guid and int. int seems smaller, faster, easy to remember, and keeps a chronological sequence. However, as for Guid, the only advantage I see is that it is unique. In which situation do you think SQL server guid would be better to use? Int only seems to have one disadvantage of the number limit, which is irrelevant in many cases. So, why exactly was SQL server Guid created? It should have a purpose more than just serving as the primary key of a simple table. (Any example of a real application using Guid for something?)
This has been asked in Stack Overflow multiple times Jeff's post explains a lot about the pros and cons of using SQL Server GUI
###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, unless partial sequentiality is needed (i.e. with newsequentialid())
- 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 the 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).