views:

89

answers:

3

As the question says. The table has no primary key defined. Other tables in the database do not change after compacting, and have as well no primary key defined.

A: 

Without a primary key, Access is free to rearrange the rows of a table as it sees fit. My guess is that the effect depends on the usage pattern for the the table. When you delete a row, the database simply marks it as deleted. When you compact, it reclaims the space by overwriting deleted rows with a non-deleted rows. If a table has a primary key, then the database will be careful to preserve the row ordering during this process (which is consequently a much more expensive operation); otherwise, it won't.

Peter Ruderman
+2  A: 

"If a primary key exists in the table, compacting re-stores table records into their Primary Key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient."

ACC2000: Defragment and Compact Database to Improve Performance

"New compacting method. Compacting the database now results in the indices being stored in a clustered-index format. While the clustered index isn't maintained until the next compact, performance is still improved. This differs from Microsoft Jet 2.x where rows of data were stored the way they were entered. The new clustered-key compact method is based on the primary key of the table. New data entered will be in time order."

New Features in Microsoft Jet Version 3.0

What neither of these articles tells you is something that must be observed: that if a UNIQUE constraint (or unique index) exists on NOT NULL columns then this will be used in lieu of the PRIMARY KEY. What I haven't been able to ascertain is how ACE/Jet picks one if there are multiple UNIQUE constraints on the table: first created? first column's ordinal position? first NOT NULL column's ordinal position? Your guess is as good as mine.

onedaywhen
+2  A: 

Tables don't have order. A table datasheet may, but that's not the table, but a UI object used to display the table.

If you want to control the order of your table, use a SQL statement with an ORDER BY clause.

You shouldn't be using table view in an application.

If you don't have an application and you don't like the default order, set the ORDER BY property on the table datasheet and save it, or write a query with the order you like.

But you need to give up the idea that tables have any order at all. The mere idea is completely contradictory of the basics of SQL and set theory. You shouldn't care what order the records are actually stored in the physical database.

David-W-Fenton
"Tables don't have order" -- a logical SQL table doesn't but a physical ACE/Jet base table does e.g. why else does ACE/Jet have set functions FIRST and LAST?
onedaywhen
I don't know why those exist. I've never found them to be of any use whatsoever as there is no predictability about what they will return. Not a single one of my Access apps would break if Jet suddenly dropped support for those.
David-W-Fenton
Time to wheel out what is becoming my catch phrase: "So that's another way in which ACE/Jet is non-compliant with Standard SQL." Put non-relational features into a product and naturally people will use them.
onedaywhen
Not sure what you mean. What is noncompliant, that the results of First/Last are not predictable? By "not predictable" I mean that I see no utility in return the First/Last records of the physical ordering of the table, as that has zilch to do with your data. If that's what First/Last are defined to do, then it seems greatly at odds with all the principles of SQL I'm aware of.
David-W-Fenton
Turn it around, what about it is compliant with Standard SQL? FIRST and LAST set functions are non-existent in SQL-92. In ACE/Jet, without an ORDER BY clause you are relying on physical order for some potentially tricky yet deterministic result and a first normal form violation to boot. With an ORDER BY clause you get meaning in ACE/Jet but in Standard SQL ORDER BY is part of a cursor and not part of the SELECT clause, so you get meaning at the expense of SQL-92 compliance.
onedaywhen
In my opinion, FIRST/LAST should not exist at all. I have never found any use for them.
David-W-Fenton