Re: Gather

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

From: Craig Boyd (craig1442_at_mchsi.com)
Date: 09/23/04 

Date: Thu, 23 Sep 2004 18:03:13 GMT

While using it as a Surrogate Key does take away some of the problems
associated (since it would no longer be related to any of the other
tables in the database and used only for the sake of uniqueness), it
still leaves the developer open to quite a few potential problems. The
chief among them is that the autoincrement fires on buffered data, and
in a multiuser environment this can really wreak havoc. Also, there are
other considerations here and drawbacks to autoincrementing fields as
noted by MS in the VFP Help file. Topic "autoincrementing field values"
  see the paragraph headed "Issues Concerning Using Automatically
Incrementing Field Values"...the issues with the table header being
locked speak to a potential problem in multiuser environment, and though
not related to a discussion of the pros and cons using AutoIncrementing
fields as Primary Keys, I would say that the 35% hit in performance when
  appending table-buffered records is a pretty significant degradation.

I too use a stored procedure and a table of counters...just call the
stored proc in the default value for the field. I'm sure this is what
you are doing as well based on your description. It is the best way
I've yet to find in handling Primary Keys.

Please note that I'm not saying that autoincrementing fields CAN'T be
used as Primary Keys, I'm merely suggesting that it is not one of their
best uses and there are alternatives for Primary Key generation that are
better suited.

Alex Feldstein wrote:
> On Wed, 22 Sep 2004 20:19:25 GMT, Craig Boyd <craig1442@mchsi.com>
> wrote:
>
>
>>"This is used generally to generate Primary Keys (PK)"
>>
>>I'm of the mind that auto-increment fields should hardly ever (if ever)
>>be used as Primary Key fields. Perhaps in a small, simple single-user
>>application you could get away with this shortcut, but it is not good
>>practice and can lead to a myriad of problems. Auto-incrementing fields
>>have there place and uses, I jusst don't think this is one of them.
>
>
>
> And why not?
> I personally don't use them as I had a routine for years that uses a
> table of counters. But they are OK for Surrogate Keys.
>
> References:
> http://www.feldstein.net/newid.asp
> http://fox.wikis.com/wc.dll?Wiki~PrimaryKey
> http://fox.wikis.com/wc.dll?Wiki~SurrogateKey
>
>
> Alex Feldstein
> ________________________________
> Microsoft Visual FoxPro MVP
> Please respond in the public groups so that everybody
> can benefit from the exchange.
> Favor de responder en los foros públicos asi todos se benefician.
>
> Blog: http://www.bloglines.com/blog/AlexFeldstein
> Website: http://feldstein.net

Relevant Pages