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

Hi Shamil and John

Reading these chatty fellows is a bit difficult as they refer to quite a lot before and outside the article and many versions of SQL Server including 6.5, pack it in "if"s and different scenarios, and - as I'm not a DBA - expressions unknown by me, but to me it looks more like a discussion on indexes rather than keys with zero conclusion.

However - if that is true - if only one clustered index can be applied to a table then, of course, it has to be designed with great care considering the expected purpose and usage of the table and, of course, that may very well include a natural (compound) key. But I can't see why that should indicate a farewell to the surrogate key.

How the ADO.NET Entity Framework handles this is really out of our hands but I guess the old rule still applies: the groundwork sets the quality of the house.

/gustav


>>> shamil at smsconsulting.spb.ru 25-06-2009 21:30 >>>
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-Continues.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 >>>