The most commonly used options for key auto generation in SQL Server are
- Using identity columns
- Using unique key identifiers GUID
There are some differences to consider between both ways.
In case of identity columns
- Identity columns are auto generated with each record
- Identity columns provide uniqueness for records in a single table in which identity column is defined
- Identity columns consume space according to data type. For example identity column defined over integer data type would consume 4 bytes
In case of Unique Identifier (GUID)
- GUID provide uniqueness on platform. They may be generated by combination of MAC address and CPU clock.
- GUID is required to generate for each record through NEWSEQUENTIALID() or newID() function. NEWSEQUENTIALID() GUID in serial while newID() generates random GUID.
- GUID may consume 16 bytes of storage space on disk. It is relatively much more than 4 bytes of integer in identity column case.
So conclusion is that identity columns are efficient and suitable while working in local databases. And you have to look for GUID while working with consolidated data or data replication.
Consult BOL for further help on using GUID