September 15, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/usinguniqueindentifierinsteadofidentity.asp>http://www.sqlservercentral.com/columnists/awarren/usinguniqueindentifierinsteadofidentity.asp
April 4, 2002 at 10:00 am
I was hoping to find a nice long thread about this topic so I could hear some of the pros/cons and experiences others have had with this method.
We are currently designing a new system where the normal problems of using an identity field are not an issue (no worries about migration from other systems, all inserts of normalized tables through sProcs so we can do them in a single trip, etc).
That being the case, do we really gain or lose anything by using a UniqueIdentifier field vs Identity (other than the increase in field size)? For initial testing, I do find it easier using the identity field as I can immediately see issues / relationships vs a list of guids.
Any comments or links to other articles / discussions on this would be appreciated.
April 4, 2002 at 12:08 pm
We just used them for a good size project where I work, turned out pretty good. I'll admit looking at guid's isn't easy on the eyes. Our biggest reason was being able to generate the keys on the client. I use them a lot in objects so that they can generate their own key which works out well for adding to collections, listviews, etc. I think we'll continue to use them in some cases, not all. Depends on how well I evangelize!
Andy
September 22, 2002 at 8:15 pm
GUID's are very large numbers which cause several issues: they make joining tables less efficient, take extra cycles to generate numbers, and it requires lots of cutting-and-pasting to work data and other support issues.
Databases are shared resources and need to be used efficiently. For this reason one could use them if you're working with tables with less than 50K rows per table. But if there is a need to do a batch update in a stored proc, generating the GUIDs will require additional resources.
I strongly recommend NOT using GUIDs in place of identity columns unless there is a very specific reason for implementing them and it is used on a very limited basis. Use them for what they were intended for, not for identity columns.
September 22, 2002 at 8:22 pm
Yes, they do take additional resources. On the other hand, making round trips to retrieve the value takes resources too. Curious as to why you picked the 50k limit?
Andy
July 9, 2004 at 10:50 am
Good article. Very informative. On performance, if I use a GUID as a Primary Key with a clustered index, won't that require the database to constantly 'reorder' the data since a clustered index determines how the data is stored physically. Perhaps a larger fill factor is needed to help alleviate these concerns?
Example: If I use an integer for my PK in the orders table, new orders are always appended to the end of the table. Order 1,2,3,4,5 are all added in sequetially. With a GUID, I have no idea where an inserted row will end up physically in the table.
Thanks for the info Andy!
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply