GUIDs vs. Integers – Which is best to use as a unique identifier?

Many people typically use auto incrementing integers as a way of creating a unique identifier for a record for many different reasons. However, you can also use globally unique identifiers as well. So which one shall I use?? Take a look at the list of advantages and disadvantages of each below to help you make your decision:

Advantages of Auto Incrementing Integers 

• The storage size in order to store an auto incrementing integer is only 4 bytes, meaning that you will be able to store vast amounts of data without the integer value being a problem. 
• Fewer IO operations are required to store and retrieve an integer value rather than a GUID. 
• Integers can be incremented by any value and can be started from any value. E.g. usually it can start at 1 and increment in 1’s. You can decide to start it at 10 and then increment in 2’s for example. 
• You can tell, roughly, when the data was entered into the database as it usually occurs in chronological order. 

Disadvantages of Auto Incrementing Integers

• However, primary keys using integer values can cause conflicts when merging multiple databases as there may be duplicates. 

Advantages of GUIDs

• Because GUIDs are so unique (and random) it allows you to merge records from different tables and databases much easier with no duplicates. 
• Subtle errors when passing unique identifiers between tables can be prevented as GUIDs only occur once, unlike incrementing integers that can appear on multiple occasions throughout your database. 
• Useful from a security perspective, so if someone tries to enter through the back end of a system by editing the URL and for some reason, the security is not as tight as it should be, it would be virtually impossible for someone to guess a correct GUID in comparison to an incrementing integer that would go up by a set number each time. 

Disadvantages of GUIDs

• However GUIDs take up 4 times the amount of space as an integer number (16 bytes) which may not seem like much but when you are talking about large amounts of data with millions of rows, it can be considered a massive waste of space on both your disk and the SQL Server’s RAM as well in order to access and retrieve these from the database. 
• GUIDs reduce the performance and speed of your database when joining tables together in comparison to auto incrementing integers. 
• GUIDs are not easy on the eye, both from the developers’ point of view and the customers’. Displaying a GUID to a customer on an invoice, for example, would be pointless. 
• GUIDs cause fragmentation within your indices in the database, and thus affecting all round performance and speed of the database. Therefore they are not suitable for clustered keys and it would be much better to use INT IDENTITY () instead. 

Conclusion

GUIDs are very useful on smaller systems and for merging data from different tables or databases together. However they could potentially decrease the performance of your database on larger systems, and therefore impacting on the speed of your application.

Ultimately, the decision is down to the developer on what they prefer to use in various different circumstances.