tell me some one Guid as PK is good approach ?

if not then discuss with example why not.

one guy said :- It's a good approach if there is ever the possibility that you might need to merge across databases, such as in replication scenarios (where it's actually required).

thanks

See

http://blog.codinghorror.com/primary-keys-ids-versus-guids/

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx

http://sqlmag.com/database-performance-tuning/performance-effects-using-guids-primary-keys

One thing, usually when we create a PK , SQL Server creates unique clustered index behind the scene, that means if you have GIUD as is pretty wide datatype  and because of CI presents in NCI on all levels (B-tree) ,meaning you will have more index pages for the table, and thus SQL Server will have to work harder to read more data

good attempt but not very clear what u try to say! it will be helpful if you discuss it with example and scenario. thanks

good attempt but not very clear what u try to say! it will be helpful if you discuss it with example and scenario. thanks

Look up page splits. A GUID is random and the typical PK is also the clustered index. Since a GUID is random (yes even the sequential ones are) and a clustered index is sorted then it is a very bad idea to do this.

A GUID is 16 bytes of data, INT is 4. EVERY COLUMN in the clustered index is in EVERY non-clustered index! This bloats the size of each index and the table.

I would say if you use a GUID for your PK make sure it IS NOT the clustered index key.

@Daniel thanks for reply but this is not clear what u said "EVERY COLUMN in the clustered index is in EVERY non-clustered index! This bloats the size of each index and the table. "

can you please elaborate the above lines in details. thanks

@Uri this is not clear u said "because of CI presents in NCI on all levels (B-tree) ,meaning you will have more index pages for the table, and thus SQL Server will have to work harder to read more data"

can you please elaborate the above lines in details. thanks

Hi

Every NCI index you created on the table will contain  a key of CI on all levels of the B-tree. So if your CI is defined as CHAR(40)  for example a key is 40 bytes  as opposite  INT datatype which is only 4 bytes. Now imagine , how many index data may reside on the page (8KB)  with a CI key 40 bytes or  CI key 4 bytes......

How to Choose a Primary Key

Since a GUID is random (yes even the sequential ones are) and a clustered index is sorted then it is a very bad idea to do this.

I think the remark that even sequential GUIDs are random needs to be expanded on.  Sequential GUIDs (e.g. those assigned via NEWSEQUENTIALID() or UuidCreateSequential Windows function) are incremental, not random.  However, the initial value of a sequential GUID is reset after a server restart.  I wouldn't reboots to occur so frequently as to introduce significant fragmentation or random IO.