Wednesday, May 9, 2012

Using GUID as primary key and/or clustered index

We should avoid using column of type GUID (uniqueidentifier) for primary keys and for creating clustered index (In our case usually all Primary keys are also clustered)

The main reasons are

  1. GUID is string value so not optimal for performance
  2. Requires more storage (INT 4 bytes, BIGINT 8 bytes, GUID 16 bytes) for data and indexes. Since all non clustered indexes will be based of clustered index key if GUID is used as PK clustered then 16 bytes per key multiplied by all non clustered index entries
  3. Since GUID is server wide; it is highly likely that data change will cause higher index fragmentation than enum incremental values.
  4. Data comparison operations such as joins and where clause will be an overhead compared to enum comparison (INT/BIGINT)
  5. DB buffer cache will consume additional space while buffring the data sets which include GUID

However we will benefit from using GUID from a data replication perspective since merge replication is easily supported using GUID.

No comments:

Post a Comment