SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Uniqueindentifier Instead of Identity


Using Uniqueindentifier Instead of Identity

Author
Message
Andy Warren
Andy Warren
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15293 Visits: 2730
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Tim OPry
Tim OPry
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 247
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.



Andy Warren
Andy Warren
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15293 Visits: 2730
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
jlehew
jlehew
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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.



Andy Warren
Andy Warren
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15293 Visits: 2730
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
http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
scottwaltonmcse
scottwaltonmcse
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 93

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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search