I will not argue for using either GUID, IDENTITY columns or even natural keys. I think it all depends on your needs, if you are building a datawarehouse youâll definitely NOT stick with IDENTITY, you would probably create the Id on-the-fly, SMALLINT, INT, BIGINT or whatever suit your needs.
IMHO using GUIDs when developing Web-applications is nice and easy to deal with, for instance creating new customer, new user etc. notice the usage!! Small tables, but wide access to functionality, much easier to generate the GUID in the application and commit, anyway thatâs just my opinion. I saw another poster commented the cons of natural keys, I agree
(GUID are also un-avoidable when dealing with merge-replication, and merge-replication should normally exist in a normal sized environment where users updates/ inserts to the frontend-SQL and the data needs to be replicated to the backend)
I have read through all the posts (I believe), some misconceptions exists here and there, but the one from :
Aaron G on March 21, 2007 8:00 AM
"If you use IDENTITY as a key, the values tend to cluster on physical
data pages because they are sequential. The result is that if the most
recent rows are the most likely to be accessed, there will be locking
contention for control of those physical data pages. What you really
wanted in a key is some spread of the rows over physical storage to
avoid having every user trying to get to the same page at the same
time.â
Aaronâs comment
"And I almost missed this gem: âIf you use IDENTITY as a key, the values tend to cluster on physical data pages because they are sequential.â No Joe, thatâs what a clustered index is for. Identity keys donât have to be the clustered index and mine usually arenât.â
- Really hit my eyes⌠Aaron doesnât know how a clustered index works Joe does! Aaron missed the point regarding pages and Joe is completely right, it would be very nice to create a clustered index on different filegroups⌠however this can be accomplished at a physical level, but that is another story.
Willem Luijk on February 21, 2009 3:15 AM:
You are mixing customer id and surrogate values, donât do that! The GUID is created for relationship and not some, in your case, customerId.
I have always drawn a sharp line between Idâs and numbers; IMHO as a rule of thumb: Idâs are arbitrary values and shouldnât be used as anything else, whereas numbers are describing something.
So in your case, CustomerId is in fact a GUID, but you need to model a CustomerNo as well
And the CustomerNo can be in a friendly easy reading format, i.e. Customer: Joe Montego, could have a CustomerNo like : Montego01 , easy to understand and Mr. Montego is very happy, he doesnât need to look at some stupid insurance/ invoice or other crappy system generated, lazy dbâs/ developer/ -solution where only the nerds thinks itâs cool to have a customernumber like 4533-49900.900-88 LOL right? (and where he usually ends up with⌠yada yada⌠I only have my phonenumber and the service employee ends up with looking their own nerdy-invented stupid customerId up using that phonenumber ROTFL)