Tuesday 10 January 2012

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

 

8 comments:

  1. The blog gave me idea to use data type for the primary key My sincere thanks for sharing this valuable post.
    Dot net Training in Chennai

    ReplyDelete
  2. hello!!!
    Thanks for your informative blog!!! Your article helped me to understand the future of .net programming language. Keep on updating your with such awesome information.

    dot net training in chennai

    ReplyDelete
  3. one of the most fantastic blog. Have a great blog.Thanks for sharing this blog. keep sharing more blogs..


    Android Training in chennai

    ReplyDelete
  4. really you have posted such an interesting article. it will be really helpful to the peoples who are all working under the technology.
    android training in chennai

    ReplyDelete
  5. nice post you have been shared. it will be really helpful to many peoples. thank you for sharing this blog. before i read this blog i'm confused with this topic but now i'm clear about that.
    dotnet training in chennai

    ReplyDelete
  6. I am very glad to read your informative blog...thanks a lot for your valuable sharing
    you can also visit here .net corporate training gurgaon

    ReplyDelete
  7. • Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updating. Power Bi Online course HYDERABAD

    ReplyDelete