September 27, 2006 at 1:27 pm
We have been developing an application that uses the aspnet_db security database, and I had a question. I have been curious as to the benefits/drawbacks of using guid vs int and wanted to see if others had more information or opinions.
Pro: It is guaranteed to be unique. Inserts happen in more random order than when using an identifier int as a primary key - this will help prevent potential I/O hotspots in high transaction systems. Ability to set use gains by setting Fill Factor at level to account for updates/deletes until next index rebuild.
Con: Size - GUID stores 12 or 14 bytes compared with 4 for Int.
It seemed to me searching/joins on a GUID would be slower than those with an int but I wasn't 100% sure.
Anyone have additional thoughts?
Thanks, Erich
September 27, 2006 at 2:12 pm
Pro: GUID's can be replicated, Int's cannot.
Greg Roberts
September 28, 2006 at 1:53 am
There was a long thread on this recently : http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=29&messageid=293555
If the index is clustered, random GUIDs aree going to generate lots of page splits on inserts.
September 28, 2006 at 7:48 am
GUIDs have their place but High transaction systems is *not* an advantage for them to be considered better than ints. You can controll the *hot* spot issue by setting your clustered index on a diffrent column, create partitions, etc.
Ints are by far much faster and manageable. GUIDs do offer simplicity of uniqueness on disconnected enviroments but that also can be overcomed by adding "location" to the PK.
Merge replication or updatable subscriptions are good examples for the need of GUIDs where the asynchronuos nature of the DML happens to force their use quite a bit.
* Noel
September 28, 2006 at 1:53 pm
Int's CAN be replicated!!! Even if it is an identity field, it can still be replicated!!!!
In SQL 2005, there is a new function for generating sequential uniqueidentifiers. This resolves the page split on insert problem because the new ID generated is higher than the last.
However, int's are always much better performance wise. If you do use a uniqueidentifier, then you should make the primary key nonclustered unless you are using the new NEWSEQUENTIALID ( ) function to generate the ID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply