Default retrieval from table loaded by DTS

[Lyle H. Gray]

[MS SQL Server 7]

We load a table from a text file using Data Transformation Services. The
source file is already sorted by primary key order.

After the DTS load, the default retrieval order on the target table (select
* from targettable) appears to be random. I know that theoretically the
retrieval order from a SELECT statement isn't guaranteed, but this is the
first time that I've actually had a default retrieval not follow the
primary key order in the table. I'm thinking that what we're seeing is the
physical storage order of the rows in the table, which have been somewhat
scrambled by the way that DTS loads a text file (probably some I/O
optimization).

Is there any way that we can get DTS to load the table in the order that
the rows appear in the text file (assuming that's what our problem is)?
The Project Lead really doesn't like the fact that the default retrieval
order isn't following the primary key.

Regards,
Lyle H. Gray

[Erland Sommarskog]

Lyle H. Gray (gr**@no.spam.cs.umass.edu.invalid) writes:

[MS SQL Server 7]

We load a table from a text file using Data Transformation Services. The
source file is already sorted by primary key order.

After the DTS load, the default retrieval order on the target table
(select * from targettable) appears to be random. I know that
theoretically the retrieval order from a SELECT statement isn't
guaranteed, but this is the first time that I've actually had a default
retrieval not follow the primary key order in the table.
You must have been living in a sheltered word. What you can expect is
things like "SELECT * FROM tbl" for small tables to follow the clustered
index (which in many cases is not the PK). For larger tables, this is
less expected, as SQL Server may open parallel streams, you get chunks
have decent order internally, but the chunks themselves appear in a mess.
If you have a WHERE clause, this can lead to another index being used,
leading to another order etc.
Is there any way that we can get DTS to load the table in the order that
the rows appear in the text file (assuming that's what our problem is)?
The Project Lead really doesn't like the fact that the default retrieval
order isn't following the primary key.

Tell your project lead that there is no default retrieval order. If you
want a certain order, use ORDER BY. If you don't use ORDER BY, that
means that you don't care about order.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp