Uniqueidentifier Column as Primary Key, a worst choice

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 <