Records change sort order?

[dgunning]

I have a check register file that I am processing in access. It is a text
file in a non-standard format, so I am reading the file one line at a time.
I have a table which I empty and then add the appropriate data as I read it
from the text file. There is a check number field which is only filled in in
the check register file when it changes (one check can have charges to
several different accounts), so after all the data is read in, I then go
through the table one record at a time and copy the check number down until I
come to another check number, rinse, repeat.

But it seems like I cannot count on the records being in the same order in
the table that they are in the text file. The maddening thing is that
sometimes this works, and sometimes it doesn't. I've spent most of today
trying to figure out why this isn't working (consistantly), but have had no
success. I even added a sequential number field as I built the table and
then ordered by that field and it still doesn't work all the time. Does
anyone have an idea what I'm doing wrong here? Thanks for any help.

 

[John W. Vinson]

But it seems like I cannot count on the records being in the same order in
the table that they are in the text file.

Exactly.

A Table *has no order*. It should be viewed as an unordered "sack of data".

If you want to see records in a particular order, you must - no options - use
a sort by some field in the table. You can sort in a Query, by setting the
order by property of a table datasheet (which is really just a hidden query),
the OrderBy of a form or report - but you can't make any assumption about the
order of records in a table. They'll be displayed in whatever order is
convenient for the database engine.

 

[a a r o n . k e m p f]

CORRECTION

a table in _JET_ has no order.

SQL Server supports clustered indexes, and you could merely create a
clustered index in order to physically re-arrange the order of rows in
a table.

so sorry that David thinks that a shortcoming in _JET_ means that you
need to reinvent the wheel

 

[Larry Linson]

message

CORRECTION

a table in _JET_ has no order.

That is not a "correction", it is a misleading statement. Any "certified
DBA" would know that, by definition, relational tables are unordered. To
rely on having the data in a given order, you must retrieve with an ORDER BY
clause. In some instances, in some database engines, that may be implied.

One does not need a "clustered index", nor, indeed, an "index" at all --
simply specifying the field you want to control the order in the ORDER BY
clause of the SQL used to retrieve the data will give you the data in the
order you specify -- both in Jet, ACE, and server databases.

Jet and ACE have a Query Builder to assist you in writing SQL by a visual
approach; SQL Server has a similar interface to assist you.

SQL Server supports clustered indexes, and
you could merely create a clustered index in
order to physically re-arrange the order of
rows in a table.

IIRC, an index in SQL and other server databases, just as in Jet and ACE,
provides a performance boost when using ORDER BY, stated or implied, to
control the order in which the records are presented when retrieved, but
does not control the order in which the data is physically stored.

Larry Linson
Microsoft Office Access MVP

 

[dgunning]

Thanks, John. I was *trying* to do this processing with the table ordered by
a field that I added, but made a silly mistake that prevented this from
working. Your response led me in the right direction and I found my error;
things are working fine now. Thanks again for your help.

 

[a a r o n . k e m p f]

Larry;

you are _WRONG_.

I am so sorry to think that you think that you know Access- thus you
are the king of the world. You are not.You are stuck in the 1st grade
of the technology world.
I am so sorry to think that you take your limits in MS Access and
apply it to 'every database in the world'.

Using SQL Server, it is possible to order the records in a table.
All you have to do is create a clustered index on that field.

In general- this is done automagically when you make a PK.
But you can override this for performance reasons.

From www.sql-server-performance.com
-----------------------------------------------------------------------------
Though I have added disorder records, SQL has displayed the data page
in sequence because we have got a clustered index on empid. This is
absolutely great! Adding a clustered index to the table has
physically reordered the data pages, putting them in physical order
based on the indexed column.
-----------------------------------------------------------------------------
Create a Clustered Index

Now, let us create a unique clustered index on empid column to see how
it affects the data that is stored in SQL Server.

CREATE UNIQUE CLUSTERED INDEX DummyTable2_EmpIndex
ON DummyTable2 (EmpID)
GO

Execute:

Select EmpID From DummyTable2

Here are the results:

Empid

1

3

4

6


Now, execute the DBCC ind (dbid, tabid, -1)

DBCC TRACEON (3604)
GO

Declare @DBID Int, @TableID Int

Select @DBID = db_id(), @TableID = object_id('DummyTable2')

DBCC ind(@DBID, @TableID, -1)
GO

Here are the results:

PagePID
IndexID
PageType

26411
1
10

26410
0
1

26412
1
2


MS SQL generates a page (pagetype = 10) for an index and explains the
page allocation details for an index. It shows the number of index
page have been occupied for an index.

Now, let us view the page 26410 and 26412 and see the page details.

DBCC TRACEON (3604)
GO

DBCC page(10, 1, 26412, 3)
GO

Here are the results:

FileID
PageID
ChildPageID
EMPID

1
26412
26410
0




The output display many columns, but we are only interested in four of
them as shown above.

This will display the output of the index page, which has got link to
data page (ChildPageID). EMPID is an index column will contain the
starting row of the page.

Now, let us view the page 26410 and see the page details.

DBCC TRACEON (3604)
GO

DBCC page (10, 1, 26410, 3)
GO

Here are the results:



Record Type = PRIMARY_RECORD

EmpId = 1

EmpName = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa



Record Type = PRIMARY_RECORD

EmpId = 2

EmpName = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb



Record Type = PRIMARY_RECORD

EmpId = 3

EmpName = cccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

Though I have added disorder records, SQL has displayed the data page
in sequence because we have got a clustered index on empid. This is
absolutely great! Adding a clustered index to the table has
physically reordered the data pages, putting them in physical order
based on the indexed column.


-----------------------------------------------------------------------------