[AccessD] Access and SQL Server

Hi Jim

I'm not so sure about that. As far as I know, maintenance cost of a an autonumber index is close to zero for adding records, zero for updates of other fields, and tables that large typically are for appending/reading only.

But, of course, scenarios exist where you have to optimise where possible.

/gustav


>>> jimdettman at verizon.net 07-03-2011 16:22 >>>
Gustav,

  Well you may shoot yourself in the foot at times with that approach.

  In the book/author linking table example I gave, what if you had millions
of rows?  Your going to maintain an extra index on the auto number key
simply because you want one when the book ID/Author ID works fine as a key
and you need a index on it anyway?

  Sorry, but that just doesn't make any sense to me.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Monday, March 07, 2011 10:18 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Access and SQL Server

Hi Jim

In, say, a grid you need to be able to identify any row quickly and easily
even if the table of this grid is the mother of all tables. 
Nothing beats an Id of autonumber in this respect: Always the same name,
same data type, same behaviour, same methods, same everything.

I realise it may require an index more, but that disadvantage is ignorable
compared to the huge advantages gained by a consistent use of an autonumber
Id for every table - which to me includes any lookup table as well. It makes
life safe and so much easier. No considerations: Is this a tiny table? Or a
lookup table only? Or?. Just do it, add the Id, and move on.

/gustav


>>> jimdettman at verizon.net 07-03-2011 15:29 >>>

I would not use an auto number as a key until I needed to use it as a FK
some where.