asked
Using GUID as primary has any limitation
I have a db which has GUID as primary keys in most of the object. I want to is it has any limitation and yes then what? Can I get a link of best practice DB design? Urgent help.
You design the DB how you need, there is no general best practice as it all depends on requirements. MS uses GUID's for ID's, agent jobs for example all have a guid at their identifier. But that being said, I generally would use a sequential GUID for an ID column where possible ( NEWSEQUENTIALID() ) instead of a random GUID ( NEWID() ), to ensure that the ID's go in order and thus don't cause to much index fragmentation. If you go NEWID() check fragmentation regularly on the indexes to see if you need regular rebuilds/reorgs to keep performance inline
Hi, I wouldn't recommend using a GUID as a primary key. A GUID is more or less randomly sorted which will lead to fragmentation. Even when using NewSequentialID as default value, it might lead to fragmentation if key is often generated in other systems (which is often a reason to use GUID). Also, if you use the default when creating the primary key, it becomes the clustered index for the table, which will both make the table fragmented and will also use up unnecessary space in the database. A clustered index is duplicated on leaf level on each non clustered index, and therefore all your leaf nodes of non clustered indexes will have 16 bytes extra. Plus all foreign key constraints from other tables will have a 16 byte GUID as an FK-column, with a nonclustered index. So if you can, I would suggest using an integer as a surrogate key instead of a GUID. If you absolutely need a GUID to identify rows in the database, I would probably use the GUID as a unique index, and use an integer datatype as clustered primary key. That all depends of course on how large the table is, how many other indexes you have etc. There's really no "on size fits all". There's a recent SqlMag-article about GUIDs as primary keys.
http://sqlmag.com/database-performance-tuning/clustered-indexes-based-upon-guids
18 People are following this question.
