Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Jun 25 12:19:32 CDT 2009
Hi Jim, I meant to use GUID just as "object Identity" values not as primary surrogate keys. There is an issue with GUIDs if they are used as Primary *clustered* keys/indexes on MS SQL because MS SQL server keeps table's rows physically sorted according to the value of clustered key and because generating unique GUIDs in ascending order is an additional "trouble". Also clustered key/index is used as *pointer* for other indexes of a table - therefore it's important to have clustered key value length as small as possible - http://stackoverflow.com/questions/812891/ms-sql-index-which-should-be-clust ered ... If a natural (compound) key is used as primary key/clustered index then table's rows get stored in a natural sort order... I guess (I can be wrong but such algorithm exist for more than 20-30 years for sure - it was used in VSAM - B+ - trees AFAIKR (http://www.slideshare.net/guestcc2d29/ardbms )) that if a (compound) natural key is used as Primary key and clustered index then such index's values are getting heavily compacted, and other indexes of a table, which use this clustered index as a pointer get saved a lot of cyber space - therefore using natural key as a primary key and clustered index can be even more profitable/advantageous from technical point of view than using Int/BigInt surrogate Identity keys as the latter ones do not get compacted that well as natural keys' values... Please feel free to argue. :) Thank you. -- Shamil -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Thursday, June 25, 2009 3:41 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Primary keys and entry points (was: Learning .Net -- PHP Instead?) The profox list currently has a discussion running on the use of GUIDs as surrogate keys. It seems quite a few developers there have been using them for sometime. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Wednesday, June 24, 2009 7:11 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Primary keys and entry points (was: Learning .Net -- PHP Instead?) Hi Gustav, Yes, "the single exit point is not a B/W discussion" (as most of the others we keep under this thread) - did I say I'd not use the same construct you presented in your posting? - in fact I mean I can use both depending on how I will come to my solution/what case I will program first... As for "PK great debate" - I'm now just guessing that ADO.NET Entity Framework could break one of the last and most "rigid" of my habits to use AutoNumber/Identity PK for all and every tables, and not only for lookup tables but also for relation tables: using of AutoNumber/Identity PK comes IMO mainly from technical/common sense (but again based on technical issues(?)) reasons, and ADO.NET Entity Framework, I guess, can "annul all that reasons", and cascade update can help to solve "fragile" natural primary key issue (hear, hear (JC ? :)) - cascade update is the greatest evils - and I do not use it currently...). And there could be GUID field introduced instead of Autonumber/Identity PK - but just to keep "object identity" in this becoming so distributed world with myriad times replicated data objects... -- Shamil <<< snip >>> __________ Information from ESET NOD32 Antivirus, version of virus signature database 4188 (20090625) __________ The message was checked by ESET NOD32 Antivirus. http://www.esetnod32.ru