For inserting a unique value for each record in a table, we can either create a GUID (Globally Unique IDentifier) with the help of the function NEWID() (or) create an IDENTITY column which would take care of inserting the unique value itself.
The major advantage of using GUIDs is that they are perfectly unique :) We can blindly use this if we are planning to consolidate records from different SQL Servers into a single table. The disadvantage is that the value of GUID are quite BIG. Hope it won't be too much if I say that this is "one" of the biggest datatypes in SQL Server. Due to that if we create an Indexes on a GUID field it would have a considerable performance hit. Other disadvantage which I see is it is not that easy to understand / remember a GUID value.
On the flip side, we can use IDENTITY column which would most probably be an Integer field. Due to the less space it occupy it has an upper hand when we compare this with GUID with respect to performance. However, since it is server-generated, an insert into the database requires that we query the database for the new key value. This also complicates the process of submitting new parent and child data.
Also, there is no standard for auto-increment functionality across databases. Relying on GUIDs can simplify some of these issues, since you're generating your key values at the client. If at all you are planning to port your SQL Server database to some other database then you might run into problems!! Not all databases natively support GUID. Even in those cases the work around is to cast the GUIDs as strings :)
So in my opinion we need to decide whether we can use GUID or IDENTITY column on a case by case basis.
The major advantage of using GUIDs is that they are perfectly unique :) We can blindly use this if we are planning to consolidate records from different SQL Servers into a single table. The disadvantage is that the value of GUID are quite BIG. Hope it won't be too much if I say that this is "one" of the biggest datatypes in SQL Server. Due to that if we create an Indexes on a GUID field it would have a considerable performance hit. Other disadvantage which I see is it is not that easy to understand / remember a GUID value.
On the flip side, we can use IDENTITY column which would most probably be an Integer field. Due to the less space it occupy it has an upper hand when we compare this with GUID with respect to performance. However, since it is server-generated, an insert into the database requires that we query the database for the new key value. This also complicates the process of submitting new parent and child data.
Also, there is no standard for auto-increment functionality across databases. Relying on GUIDs can simplify some of these issues, since you're generating your key values at the client. If at all you are planning to port your SQL Server database to some other database then you might run into problems!! Not all databases natively support GUID. Even in those cases the work around is to cast the GUIDs as strings :)
So in my opinion we need to decide whether we can use GUID or IDENTITY column on a case by case basis.
Comments