Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Uniqueindentifier Instead of Identity Expand / Collapse
Author
Message
Posted Saturday, September 15, 2001 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 12:38 PM
Points: 6,705, Visits: 1,682
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
Post #1031
Posted Thursday, April 04, 2002 10:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, March 30, 2014 6:06 AM
Points: 160, Visits: 245
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.



Post #21532
Posted Thursday, April 04, 2002 12:08 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 12:38 PM
Points: 6,705, Visits: 1,682
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
Post #21533
Posted Sunday, September 22, 2002 8:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 13, 2009 3:25 PM
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.



Post #21534
Posted Sunday, September 22, 2002 8:22 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 12:38 PM
Points: 6,705, Visits: 1,682
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
Post #21535
Posted Friday, July 09, 2004 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 03, 2014 11:22 AM
Points: 23, Visits: 88

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




Post #125730
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse