AddToAny
Sat, 11/21/2009 - 17:48
How to decide which key to used in your application? Whether to use AutoIncrement or UnqiueIdentifier.
Please tell me the different scenarios for using these keys.
Autoincrement is a good way to ensure the uniqueness of every row (Entity Integrity) in your table. AutoIncrement would suit you in most of the cases except Multiple Databases.(discussed in later part of this article).
AutoIncrement field's(Identity) datatype should be some form of Integer.
If your Application going to use more the 8 bytes for this field, than you have to go for Unique Identifier. But Wait, first calculate how much is 8 bytes (-9223372036854775808 to +9223372036854775807). If you still think that this number does'nt satisfy your needs then read ahead.
UniqueIdentifier(GUID) is also used to create unique values by using some special internal algorithm. GUIDs are 16 bytes in size, atleast double the size of a Autoincrement. This effects the performance of your database.(Indexes created would be greater in size).
So you should strictly use Autoincrement for its performance benefits over UniqueIdentifier.
Why do we have UniqueIdentifiers.
1. If your database spans multiple servers (database is present on multiple servers which are located in different parts of the world- Multiple Databases) then at some point of time you may need to merge your database in a single server. In this case, You must have GUIDs as Primary keys unless you can't gurantee Entity Integrity in your database.
2. In web portals, it is a common practice to send primary keys in URLs. A GUID is much harder to interpret, so its safe to use GUIDs for unsecured sections of your web portal.