Which Data Type is good For Primary Key

 

The most important thing of a table is defining the primary key; it could be single column based or the composite primary key.

 

In my development life, I saw a lot of primary key with different type or patterns.

Such as single decimal number,  some use character with decimal numbers  and  some others use character+decimal+special character like  "SOM/001/2011-2012" like this.

 

When I am working with SQL server, it always my mind, that what is the better method to implement primary key rather what data type I choose to implement. For searching the suitable answer I read several articles related to it.

 

One thing is clear, Microsoft provide you the data type UNIQUE IDENTIFITRE  wit NEWID() function to generate the world wide unique primary key. It 16 byte binary value and called globally unique identifiers (GUIDs).  It takes your processor ID, NIC card ID etc to generate the Primary key.

 

But is it a good data type for a primary key?

 

To get the suitable answer, we have to understand the definition of the primary key.

 

1.       It is unique and NOT NULL.

2.       The primary key contains the default clustered index (Physically sorted environment).

So, is the UNIQUE IDENTIFIRE is the good choice for cluster indexed data? In my view it is not. If your primary key is not well indexed is it going for good quality of performance?  No not at all.

 

So in my view

 

For primary key always choose the Integer (int) as best data type.

 

If you're primary key range is very large and not supported the range of Integer then go to Big integer (bigint).

 

Please review my article, and give me suitable comments related to this as all that I mentioned is not listed in MSDN or any other MS guide line. It is just my point of view only.

 

Posted by:  MR. JOYDEEP DAS