Re: To AutoIncrement or not to AutoIncrement

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 07/19/04 

Date: Mon, 19 Jul 2004 12:38:16 -0400

"Scott Meddows" <scott_meddows_no_spm@tsged-removeme.com> wrote in message
news:eXqTryabEHA.3420@TK2MSFTNGP12.phx.gbl...
> I don't think there is anything wrong with Identity. I'm just wondering
the pros and cons of each.
>
> "Don't use Autonumber or Identities. This is just lazy. It gives you more
functionality later on changing data and you
> won't have problems when importing data.
> Example: Your administrative person made several data entry errors, and
you have deleted all the record just entered.
> If you want to run an update query to update the ClientID (and identity
column), and want to change the identity values
> from '130' to '100' to fill in the gaps - you can't - because the column
is not updateable.

    Why would you want to do that? Surrogate keys are not supposed to be
meaningful. They are used SOLELY for improving JOIN performance. It
doesn't matter if the ID is 130 or 23788237, or if they're in perfect
sequential order or not.

> Solution: Use a generic function in your application to generate
identifiers, or use the uniqueidentifier datatype with a
> default of NEWID(). The first solution is preferred because the size of
the column can be smaller than the 16 bytes (so
> it more efficient), and no round-trips are required to get the NEWID()
value that the database generated."

    This makes no sense, especially when taken with the above quote; if you
care about sequence, why would you use a GUID? Store something meaningful
(e.g. a timestamp) if you need sequence. Do not confuse surrogate keys with
actual data.

> Also, I am using transactions to ensure that there is only one insert
going on at a time.

    Why? You're fighting against SQL Server, which was designed to allow
simultaneous data modification. Why not use the server instead of trying to
reinvent it?

Relevant Pages

  • Re: Autonum field for Relationships and Replication
    ... > that due to the PK being a single autonumber field, ... that's risking your database because it depends on the ... enforce your definition of uniqueness. ... there's no reason to program your sequence. ...
    (microsoft.public.access.replication)
  • Re: Renumbering records
    ... use a two-column sequence instead, ... In fact, I don't really want true replication, but ... started getting erratic results in my autonumber field. ... Then the next time you copy an Access database to ...
    (microsoft.public.access.replication)
  • Re: updating values
    ... Access is a relational database ... ... Access tables store data, but you'll get better use of ... The other part is getting the sequence numbering. ... Access Autonumber is intended to provide a unique ...
    (microsoft.public.access.formscoding)
  • Re: Autonumber Question
    ... The problem with using Autonumbers as a sequential key is that you can (and ... will) lose numbers in the sequence. ... the autonumber value that would have been used is lost. ... >> Still is a good design concept to never use an autonumber field if it's ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Auto Numbering
    ... An autonumber is designed only to guarantee unique values not sequential ... If an uninterrupted sequence is important then don't use an autonumber ... As the Handling Agent's name is being stored in a column in the table ... person and a primary key, which can be an autonumber, such as ...
    (microsoft.public.access.gettingstarted)