Record order in an mdb database

[John Dann]

This is more of a concern or curiosity than a major problem, but I'd
still feel more comfortable if I understood why it was happening:

I'm using an Access type database to store data that is logged
automatically by another program eg every 10 minutes of every day. The
timestamp is included as a datetime field in every record and is
assigned as the primary key.

The Access database is populated automatically and iteratively via a
utility using ado.net in periodic batches from a logfile that
contains the raw logged data in strict sequential datetime order and
so I was assuming that the datetime sequence would be preserved in the
sequence of database records.

When I view the database from within Access, all seems well, ie the
record order is exactly as expected in strict datetime sequence. (But
maybe Access is automatically sorting on the primary key?)

But if I retrieve a subset of data from the database into a dataset,
eg by:

SELECT Date_Time FROM Tablename WHERE Date_Time BETWEEN dt1 and dt2

and examine the dataset in a grid or listview then the records are in
a somewhat irregular sequence of blocks of datetime values. While the
general trend of the sequence is approximately from earliest to latest
the more detailed sequence is in blocks of 20-30 records that may be
out of exact datetime sequence.

Don't know whether I've explained this very clearly but if it is
comprehensible then I'm curious to know why the dataset doesn't get
returned with datetime values in strict sequence.

John Dann

 

[Guest]

The actual storage in an RDBMS (Relational Database Management System) is not
necessarily in order of insert. The clustered key will set up physical
ordering. If there is no clustered key, the records will be shoved in where
there is room.

With this in mind, you should ALWAYS have an ORDER BY clause in your SQL if
order matters to you.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Paul Clement]

¤ This is more of a concern or curiosity than a major problem, but I'd
¤ still feel more comfortable if I understood why it was happening:
¤
¤ I'm using an Access type database to store data that is logged
¤ automatically by another program eg every 10 minutes of every day. The
¤ timestamp is included as a datetime field in every record and is
¤ assigned as the primary key.
¤
¤ The Access database is populated automatically and iteratively via a
¤ utility using ado.net in periodic batches from a logfile that
¤ contains the raw logged data in strict sequential datetime order and
¤ so I was assuming that the datetime sequence would be preserved in the
¤ sequence of database records.
¤
¤ When I view the database from within Access, all seems well, ie the
¤ record order is exactly as expected in strict datetime sequence. (But
¤ maybe Access is automatically sorting on the primary key?)
¤
¤ But if I retrieve a subset of data from the database into a dataset,
¤ eg by:
¤
¤ SELECT Date_Time FROM Tablename WHERE Date_Time BETWEEN dt1 and dt2
¤
¤ and examine the dataset in a grid or listview then the records are in
¤ a somewhat irregular sequence of blocks of datetime values. While the
¤ general trend of the sequence is approximately from earliest to latest
¤ the more detailed sequence is in blocks of 20-30 records that may be
¤ out of exact datetime sequence.
¤
¤ Don't know whether I've explained this very clearly but if it is
¤ comprehensible then I'm curious to know why the dataset doesn't get
¤ returned with datetime values in strict sequence.
¤
¤ John Dann

Microsoft Access displays data in the order that it was added to the database by default so it's
just a coincidence that it happens to be ordered by your timestamp. You can also change this at the
table level and save the sort order setting.

If you're working through the Jet database engine and creating a client side Recordset or ADO.NET
DataTable you should use the ORDER BY clause to order the data properly.


Paul
~~~~
Microsoft MVP (Visual Basic)