I have simple SSIS package where I import data from flat file into SQL Server table (SQL Server 005). File contains 70k rows and table has no primary key. Importing is sucessful but when I open SQL Server table the order of rows is different from the that of file. After observing closely I see that data in table is sorted by default by first column. Why this is happening? and how I can avoid default sort? Thanks.
+5
A:
In general no order is applied if there is no ordering in the select query.
What I have noticed is that the table results might return in the order of the primary key, but this is not gaurenteed either.
So all in all, if you do not specify a ordering, no ordering can be assumed.
astander
2010-04-15 05:29:31
well said......
marc_s
2010-04-15 05:32:55
File has no unique field and I want order to be same as that of in file. How can I do that?
Pramodtech
2010-04-15 05:44:28
+3
A:
You cannot rely on ordering unless you specify order by in your SQL query. SQL is a relational algebra that works with sets. Those sets are unordered. Database tables do not have an intrinsic ordering.
It may well be that the sets are ordered due to the way the data is retrieved from the tables. This may be based on primary key, order of insertion, clustered key, seemingly random order based on the execution plan of the query or the actual data in the table or even the phase of the moon.
Bottom line, if you want a specific order, use order by. If you don't want a specific order, the DBMS is free to deliver your rows in any order, including one based on the first row.
If you really want them sorted depending on the position in the import file, you should add another column to the table to store an increasing number based on its position in that file. Then use order by using that column. But that's a pretty arbitrary sort order, you're generally better off choosing one that makes more sense to the data (transaction ID, date/time, customer number or whatever else you have).
If you want to avoid the default sort (however variable that may be), use a specific sort.
paxdiablo
2010-04-15 05:34:37
Thanks pax! It makes sense to sort by data in the file and I'll see how is that possible. Anyway if that is not possible can I use your first suggestion to use auto increment column? will it give me same order as that of file when I sort by this column?
Pramodtech
2010-04-15 05:50:14
Yes, if you can assign the numbers correctly. But you need to watch out for multiple imports of different files, they'll need to get ever ascending numbers. And before you decide to use an auto-increment column, you should check that the insert order that the import uses is that of the file. But _still_ I doubt the usefulness of that order. Why is it important that data be sortable by position in file?
paxdiablo
2010-04-15 05:55:03
I think I can just specify column as Identity(1,1) and number will be in asceding order so I need not to worry about assigning numbers correctly. I'll give try on this. Order is important for client and I have no clue for what but it's their demand!
Pramodtech
2010-04-15 06:10:47
Just make sure the import process inserts in the same order as the file. I can imagine a _possible_ scenario where it loads 50 records into memory at a time, then inserts those in reverse order. Yes, unlikely, I know, but you _should_ check, just in case the guy that coded the import was a sociopath :-)
paxdiablo
2010-04-15 06:13:15