Flat file to table - rows out of order

sadie519590

Hi,

I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.

In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.

All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.

Is this normal

Thanks

Jamie Thomson

Is it normal Well...its not not normal!

There is no concept of order in a database table. You should never assume that rows will get returned to you in the order that (you assume) they were inserted.

-Jamie

sadie

That is not my understanding. For example, if you create a table, then insert a bunch of rows, one at a time, they will most definitely be returned in the order they were inserted. I have *never* seen an exception to this.

Perhaps the SSIS package is not inserting the rows in the order they are in the file

Anyhow, I could be wrong, but this goes against my experience completely.

Phil Brammer

Not to sound mean or anything, but Jamie is absolutely right. There is no such thing as ordering in database land. Just because your experience "proves" otherwise, doesn't make it fact. The only way to guarantee order is to use an ORDER BY clause on your SQL statement which only controls the PRESENTATION of the data, not the way it's stored.

Do you have a situation that the records are out of order when ordering by an identity column, or are you merely using a "select * from table" statement without an ORDER BY clause

This is perfectly normal behavior. You might want to add a sort transformation right before the destination. But still, there are no guarantees that the data will be stored "in order."

David Frommer

By its definition, a database table is an unordered set of rows. While "most" of the time, a select without an ORDER BY clause will return the rows in the order they were entered, it is never guaranteed. The only way to guarantee retrieving rows in the order you want is with an ORDER BY clause on the query.

Jamie Thomson

There are many factors that influence the order in which rows are returned. The most obvious being the presence of indexes.

Other possible causes may be the number of processors, what data is cached, datafile placement, datafile fill factors, hard drive configuration. There are a million and one things.

These same factors that affect the retrieval of data can also affect the insertion of data. Hopefully you can see how the order in which data is retrieved can be affected.

There is no concept of order in a database table. Period.

-Jamie

sadie

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them.

Anyways, thanks.

Jamie Thomson

sadie519590 wrote:

Ok, ok - just had to make sure. As this goes against anything I have ever seen before. I've only been using SQL Server a couple years now, so there's a lot of things I haven't seen. This is one of them. Anyways, thanks.

No worries. All the training courses in the world wouldn't have taught you this. The only way you learn a product is by using it. I've been using this damn thing for seven years now and I only know a fraction of it

-Jamie