:::: MENU ::::

Using identity columns or UniqueIdentifier for auto generated keys

  • Aug 22 / 2009
  • 1
Data Modeling and Database Design, Data Types, DBA Interview questions, dbDigger, T-SQL Interview Questions

Using identity columns or UniqueIdentifier for auto generated keys

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

  • Is there any limitations regarding System Functions and Arithematic operators while using Unique Identifier (GUID)?

Consult us to explore the Databases. Contact us