Re: Autonumber Fields

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

What would you do to guarantee uniqueness in a Contacts table or some such
involving names and addresses, in light of the fact that names and addresses
are subject to change?

SQL underlies Access queries. The design grid is a sort of SQL GUI (as I
understand it). So I think you're saying that displayed order (e.g. sorted
by last name) is not what you are talking about when you talk about physical
order. If I understand, you are saying that the structure of the index
determines the order on the disk, not the order in the table when it is
viewed directly.

I have a database that includes an Employees table. The primary key is the
EmployeeID. With it to do over again I might have used something else,
because it is at least possible that they will one day change the format of
EmployeeID, which is just a sequential 4-digit number. In most cases I sort
the employee names by last name. Would adding an index to that field maybe
speed up some operations, even though the list is rather small (fewer than
100 current employees, along with a number of former employees)?

"Craig Alexander Morrison" <designers@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:%23ieWSyFwFHA.3312@xxxxxxxxxxxxxxxxxxxxxxx
> Physical is Disk!
>
> Clustered Index can only be a Primary Key.
>
> You may need several fields to define uniqueness, several fields can make
> up an index and a primary key which is actually an index also as opposed
> to a field.
>
> Order can be anything you want whenever you want it using SQL. If you are
> going to sort by a specific field or combination of fields you may
> consider adding an index to that field or combination of fields.
>
> Indexes speed things up when sorting and analysing data, they can slow
> things down if you are inserting data, especially bulk updates.
>
> --
> Slainte
>
> Craig Alexander Morrison
> Crawbridge Data (Scotland) Limited
> "BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
> news:%23FbFy7EwFHA.724@xxxxxxxxxxxxxxxxxxxxxxx
>> Thank you for the explanation. It makes sense that it has to do with
>> physical ordering in a table rather than on the disk. Having said that,
>> I cannot discover the connection between indexes, the table's Order By
>> property, and anything else that suggests an order within the table, on
>> the actual order of records in the table. Order By, in particular, seems
>> to accomplish nothing.
>> Regarding John Doe, it may well be a name used by more than one person.
>> How does this fit in with clustered indexes? I may need duplication in
>> that field.
>> Suppose I wanted to create a clustered index in an Access table. How
>> would I do that? The term does not appear in Access Help, and
>> discussions of the subject tend to assume the reader knows what a
>> clustered index is and how to create one. Even if one is created, what
>> benefits will I notice?
>>
>> "Craig Alexander Morrison"
>> <designers@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:u$06LmEwFHA.2924@xxxxxxxxxxxxxxxxxxxxxxx
>>> Jet 4.0 and 3.5 (and earlier versions) cluster on the Primary Key and a
>>> Compact will keep it managed.
>>>
>>> Indeed a clue to this is the Registry entry for:
>>>
>>> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines.
>>> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines
>>>
>>> both contain the setting CompactByPKey.
>>>
>>> I am not sure what would happen if you changed the above setting from 1,
>>> I
>>> expect 0 would skip the clustering - I am not sure if any other setting
>>> would be valid.
>>>
>>> SQL Server generally clusters on the Primary Key, however, you can
>>> select
>>> another index.
>>>
>>> AutoNumbers are very poor devices to truly define a unique record in the
>>> real world, You can enter the name John Doe 1,000,000 times in your
>>> database
>>> if the Primary Key is an AutoNumber and you have failed to do something
>>> to
>>> prevent the creation of 1,000,000 John Doe's. You may have 1,000,000
>>> unique
>>> records but so what?
>>>
>>> Recommending the AutoNumber as Primary Key without pointing out the
>>> dangers,
>>> and suggesting the definition and declaration of the natural key (should
>>> one
>>> exist), is unwise.
>>>
>>> BTW A clustered index is merely a physical ordering of the records in a
>>> table in the database file. Using the true natural key (should one
>>> exist) as
>>> the primary key will ensure that all the records with a similar PK will
>>> be
>>> physically located next to each other. Using an AutoNumber (sequential
>>> order) as PK will mean the records are clustered according to their
>>> creation
>>> order. Using IDENTITY and AutoNumber as PK defeats the purpose of PK,
>>> this
>>> is not so bad in SQL Server as it allows you to choose something more
>>> sensible if you have an IDENTITY field in use as PK.
>>>
>>>
>>> --
>>> Slainte
>>>
>>> Craig Alexander Morrison
>>> Crawbridge Data (Scotland) Limited
>>> "BruceM" <bamoob@xxxxxxxxxxxxxxxxxx> wrote in message
>>> news:uSSscODwFHA.708@xxxxxxxxxxxxxxxxxxxxxxx
>>>> That you disagree with somebody does not make that person wrong. Roger
>>>> has provided a wide range of assistance in this forum, and has made
>>>> samples available on his web site. Based on his track record I would
>>>> be
>>>> inclined to follow his advice. If you are trying to convert people to
>>>> the
>>>> idea of using clustered indexes, a very basic discussion of what they
>>>> are
>>>> would be most helpful. I have taken your suggestion to look at Google
>>>> groups. There is indeed a lot of discussion, but I have not yet found
>>>> how
>>>> I would create a clustered index if I wanted to. My databases with a
>>>> few
>>>> thousand records seem to work just fine. Why would I want to put extra
>>>> effort into something that already works well? I know you have posted
>>>> code that includes MAKE TABLE or some such, but the utility of such
>>>> code
>>>> is not clear. The other thing I noted in Google groups is that most of
>>>> the discussion of clustered indexes seems to be in discussions about
>>>> SQL
>>>> server.
>>>>
>>>> <peregenem@xxxxxxxxxxxx> wrote in message
>>>> news:1127461712.642035.125440@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>>>>
>>>>> Roger Carlson wrote:
>>>>>> Autonumber fields make excellent Primary Keys.
>>>>>
>>>>> You've misunderstood what PRIMARY KEY means. An unique integer which
>>>>> has no meaning in respect fo the entities being modelled makes a lousy
>>>>> PRIMARY KEY. Google for "clustered index" in the Access groups.
>>>>>
>>>>> An autonumber is a convenient uniqueifier but unquieness for its own
>>>>> sake make not be such a good thing.
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Help With Slow SQL Query
    ... ALTER TABLE Employees ADD CONSTRAINT PK_Employees PRIMARY KEY ... would not use a clustered index on the PK. ... >>Indeed, for this query, having a clustered index on ...
    (microsoft.public.sqlserver.server)
  • Re: Re-ordering of Tables
    ... Sorting does not physically alter how the data is stored. ... I don't disagree that a clustered index can enhance ... With respect to the choice of a Primary Key or clustered index ... In the case of a compact and repair, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: orace sql query
    ... Puget Sound Oracle Users Groupwww.psoug.org ... "If you try to trick Oracle by realizing that a primary key can be ... Oracle will use the index T_IDX to enforce the primary key. ... "In order to enforce uniqueness, and that includes a UNIQUE constraint ...
    (comp.databases.oracle.server)
  • Re: Autonumber Fields
    ... this special meaning is clustered index. ... Access/Jet and that's using PRIMARY KEY. ... > autonumber fields are lousy in terms of real-world uniqueness. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Move table without a clustered primary key?
    ... Now, SQL Server do not noccesarily need to move any of the actual data, ... but you *have the option* to move the data to whatever filegroup you specify in the MOVE TO option. ... Is this only relevant when the table is not going to have a clustered index, ... whether or not the clustered index supports a Primary Key. ...
    (microsoft.public.sqlserver.server)