UNIQUEIDENFIER and a primary key constraint..

I would lke to share some tips which can affect performance  big way.

 

This is about "uniquifiers " datatype...although i hope it wont be favourite for many after reading this.

 

When you chose your cluster index which is variable length column then you are adding 4 bytes to every row which you is an overhead.

 

Now interesting point is that UniqueIdentifier are also stored in variable blocks, which means you are adding overhead of 4 bytes every time to your row.

 

Now you can imagine what I am trying to tell you :) ......Ofcourse you are performing more IO unneccesary by adding those extra 4 bytes.

 

Also uniqueidentifier is not sequential, which means sql server has to work more harder when you make it as a clustered index(which works best sequentially).

 

In a nutshell, UniqueIdentifier  is very big data type and when you are inserting large number of rows this cause huge fragmentation and consequently the size of your cluster index grows huge as well.

 

Please use data type like  int and  make your cluster index narrow, sequential(uniqueidntifier is non sequential) and with fixed size columns.

 

Believe me this will make your life much easier :)