CELKO (11/7/2012)

---

Can any one suggest me for each tables primary key should be Bigint as Primary Key or [uniqueidentifier] as PRIMARY KEY ?

This is a silly question and you need to read a book -- ANY BOOK -- on RDBMS> A key is a subset of the attributes of an entity which uniquely identifies each entity in the set. It is not a particular data type!

When you look for a key, you start with the standards of your industry. I would estimate that this covers ~80% of the properly defined systems -- VIN, ISBN, UPC, EAN, etc. which can be verified and validated via a trusted external source. In the old days, this took time; you can Google it today.

After that, there is a natural key in the data, such as (longitude, latitude or HTM), store, cash register, ticket_nbr, etc which can be verified and validated in the reality of the data. This is ~18% of the cases. if you have no natural key, then your schema is probably wrong.

Then, if that fails and we are looking at <2% of all situations, we invent a key with check digits, validation rules and an audit trail within our enterprise. This is a great screaming pain IF you not do it right. This is why industry standards exists -- people got tired the pain Would you like to do Retail without standard UPC barcodes on products? We did that for centuries before barcodes.

So, Newbies prefer to do it wrong by using auto-increments or other proprietary stuff that cannot be verified or validated BECAUSE it is easier than real RDBMS design. They want to have a magical, universal "one-size-fits-all" answer that does not require you ACTUALLY understand the problem domain.

A key without validation and verification will only lead to dirty data. And after all the overhead of the “Magical Universal Elixir UID” they still have to assure that the relational key is unique.

On a scale from 1 to 10, what color is your favorite letter of the alphabet?