Uniqueidentifier Column as Primary Key, a worst choice


 GUID or int as Primary key ???


Though it is not necessary that your Primary Key column is always a cluster index too. By default, Sql Server creates cluster index on column or group of columns which you have declared as your table PK, and most of DBAs don’t like to go against this default behavior of Sql Server.

But problem arises when for uniqueness a column with uniqueidentifier data type is added, for surrogate key to make it Primary Key, finally for your table.

The GUID is a wide column (16 bytes to be specific) and contains a unique combination of 33 uppercase and numeric characters. This column because it is the primary key is going to be stored in, of course, the clustered index .

Also, if a GUID is used instead of an integer identity column then the 33 characters need to be matched for each row that is returned using that column in the where clause.


If a high volume of inserts are done on these tables then GUID’s being large will contribute to page splits, as will the fact that NEWID() generates a random value, which could place a new record on any of the data pages will cause performance problems.


Recommendations

  1. INT must be used as Primary Key instead of GUID because:
  2. INT takes only 4 bytes, saving your physical and memoray storage.
  3. INT as primary key (identity) creates incremental values resulting less then 1% of indexes fregmention during heavy insert.
  4. There are T-SQL operators available for INT like >,= and <
Advertisements

Posted on July 22, 2009, in Database Design. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: