[AccessD] Clustered Index Debate - Was: RE: Primary keys and entry points

Hi John,

Let me note that this thread is discussed in context of

"ADO.NET Entity framework plays farewell for manual SQL coding". 

Please read this blog entry:

http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Cont
inues.aspx

with Anthony Thomas comment (here is one excerpt):

<<<
In either case, the table KEY (because it is "the" key) will be used more
frequently than most other attributes or composites for restriction on the
broadest set of available queries. As such, defining this key clustered has
enormous benefit, and more so than the surrogate, which is typically unknown
to the end users.
>>>

Now, after you've got read all the above, and assuming that ADO.NET Entity
Framework is used as the main data access/manipulation technology can you
still state strongly that using natural keys instead of surrogate keys
"sucks from an implementation perspective and it sucks from a speed /
storage size issue."?

Thank you.

--
Shamil

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, June 25, 2009 10:50 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Clustered Index Debate - Was: RE: Primary keys and
entry points

 > Farewell surrogate keys?

I think not.

My primary reason for surrogate keys is simply that using an ever increasing
number of fields as the 
FOREIGN KEYS down into child tables just sucks.  It sucks from an
implementation perspective and it 
sucks from a speed / storage size issue.

Your data in a great great grandchild table can be dwarfed by the FK of the
parent record.

And try refactoring the tables when the business rules change and add a
field way up the chain to 
the PK of the great-great-grandparent table.  You will be rethinking natural
keys in a hurry.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> Hi All,
> 
> Have a look:
> 
>
http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Cont
> inues.aspx
> 
> Farewell surrogate keys?
> 
> Thank you.
> 
> --
> Shamil
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Thursday, June 25, 2009 3:45 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Primary keys and entry points (was: Learning .Net
--
> PHP Instead?)
> 
> Hi Shamil
> 
> As Jim tells, if you can't guarantee that your object will live at one
> location only and can/may be mixed with similar objects from elsewhere,
use
> a Guid to identify it. As I'm sure you know, in Access this has been
> available since A97 (or A95?) where replication was introduced.
> 
> A reason to not use a Guid is ressources. It takes a little more space and
-
> random as it is by nature - you will most often need other keys for
sorting,
> a timestamp for example.
> For one project I mixed this. Simplified, a Guid was used for the Company
> table, while all other tables in some way related to the Company table and
> could use a normal Autonumber.
> In another project I (again simplified) used a compound key for the
Company
> table (or the the object identity) with one field to identify to location
> and one field with an Autonumber. If this is what you have in mind, you
are
> of course right, that the single Id autonumber must be given up.
> 
> /gustav
> 
<<< snip >>>
 

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4189 (20090625) __________

The message was checked by ESET NOD32 Antivirus.

http://www.esetnod32.ru