Re: Unique Number for each row
- From: "David W. Fenton" <XXXusenet@xxxxxxxxxxxxxxxxxxx>
- Date: Tue, 15 Aug 2006 15:48:01 -0500
"Larry Linson" <bouncer@xxxxxxxxxxxxx> wrote in
xO8Eg.55645$zc2.25436@trnddc06:">news:xO8Eg.55645$zc2.25436@trnddc06:
<pietlinden@xxxxxxxxxxx> wrote
Set the field's type as autonumber at the table level...
However, do not expect this to be the equivalent of "Row Number"
as that term is used in some other databases. Autonumbers are for
internal use, especially as surrogate keys, and to be referenced
by the foreign key fields in related tables.
It's important to be sure you do *not* think of AutoNumbers as
either identity fields or as record numbers. An AutoNumber is really
just a special kind of default value. Most fields have a default
value field. You could set a date type field to a default value of
Date() so that today's date would be inserted when the record is
created. An AutoNumber is a similar type of default value, but an
incremented AutoNumber is a default vaulue of Max()+1 (it can also
be set to random, which picks a number from the entire range of
possible values storable in a long integer field).
But, by definition, Records in relational DB Tables are
_unordered_ and ordering must be done when the data is retrieved
via a Query or SQL statement.
This is something that people who've never actually studied the
theory behind SQL don't get. Data can have an ordering in the
physical storage, but SQL manipulates that data at a level that
ignores the physical ordering entirely -- all SQL resultsets must be
assumed to be unordered until you've placed an ORDER BY clause on
them. What that means is you can't count on a SQL statement with no
ORDER BY clause returning the data in the same order every time.
Secondly, record numbers make sense *only* when you're always
retrieving or navigating the entire table. In a networked
environment, that's very inefficient, so you'd be retrieving single
records or subsets of records. What good would an absolute record
number in the whole table do you when you're retrieving only one
record or a dozen?
In general, those who demand record numbers are trying to make
Access and SQL work the way the tools they've used in the past
worked (usually xBase-derived programming practices). They need to
get away from those assumptions ASAP.
Also, those who are asking for record numbers are often trying to
process data sequentially, when SQL UPDATES, which operate on sets,
would be much more efficient.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
.
- References:
- Unique Number for each row
- From: General Fear
- Re: Unique Number for each row
- From: pietlinden
- Re: Unique Number for each row
- From: Larry Linson
- Unique Number for each row
- Prev by Date: Re: sql and passing through open args
- Next by Date: Re: WARNING: Access 2007 MVFs vs Relationships Diagram
- Previous by thread: Re: Unique Number for each row
- Next by thread: Re: Unique Number for each row
- Index(es):
Relevant Pages
|
