?
Solved

Data write to unicode file change order randomly

Posted on 2010-03-27
9
Medium Priority
?
366 Views
Last Modified: 2013-11-30
Hi,

I'm using DTS to create a unicode file which contains arabic data. What I have noticed that record ordering in the file changes randomly time to time, sometimes ordering is perfect while sometimes it changes without any reason.

Source data used is static.

Whats causing this and what can I do to prevent.

Thanks
0
Comment
Question by:wagachchi
  • 4
  • 2
  • 2
8 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 28885098
Do you have an ORDER BY clause on the query that exports the data out of SQL Server?
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 28902807
If it solves the problem, you can use GROUP BY in your query  that exports the data out of SQL Server on all non-aggregate function applied columns from your SELECT list. This will do grouping and for this ordering but only in ASC(ascending order) where as ORDER BY will do both ASC/DESC. See if functionality is met like this.

But both GROUP BY and ORDER BY work one column at a time from left to right and top to bottom as your SQL query flows i.e. for ordering/sorting & grouping/aggregation based on 1st column, then 2nd column etc.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 28926529
>>This will do grouping and for this ordering but only in ASC(ascending order) <<
I am afraid that is not correct: The GROUP BY clause alone does not guarantee any specific sort order.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LVL 1

Author Comment

by:wagachchi
ID: 29411608
Table has clustered index on the column I want to order records. Hence order by was not used in the query to output.

Since you said, I tried with order by, but still ended up same results.

Ordering issue is happening so randomly that 2 out of 5 runs would have the issue, the rest is perfect.
0
 
LVL 17

Expert Comment

by:k_murli_krishna
ID: 29414366
ORDER BY col1 ASC/DESC orders records ASC/DESC based on values in col1

ORDER BY col1,col2,.. ASC/DESC orders records ASC/DESC based on values in col1, then within that in col2 etc.

A clustered index is all the more better for usage even for ORDER BY since it is physically sorted on disk. So, it is more efficient for high cardinality(row count) tables and high cardinality(unique values column count).

Now, the order of records are retrieved is not guaranteed to be same as as order of records inserted since the disk IO does not understand all this and the data and empty pages on disk keeps changing with DELETE/UPDATE/INSERT since it will store/retrieve the easiest i.e. nearest/fastest it can.

So, keep correct ORDER BY clause and you should be good to go.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 29475582
>>Table has clustered index on the column I want to order records. Hence order by was not used in the query to output.<<
Again, unless you use an ORDER BY clause the resultset is not guranteed to be in any order.  Regardless of whether you have a clustered index or not.
0
 
LVL 1

Author Comment

by:wagachchi
ID: 30167072
I tried with order by but still ordering is not correct.

Further investigations found that, one record has unprintable character which causes the issue, once removed looks fine.

Thanks Guys.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 30227242
>>I tried with order by but still ordering is not correct. <<
This statement is not correct.  The unprintable character is considered part of the data to be sorted.  So technically it is sorting the data correctly, it is just that you are not considering the unprintable character as valid.  SQL Server cannot make that distinction.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, I read that Microsoft has analysed statistics for their security intelligence report. It revealed: still, the clear majority of windows users do their daily work as administrator. An administrative account is a burden, security-wise. My ar…
In a question here at Experts Exchange, a member was looking for "a little app that would allow sound to be turned OFF and ON by simply clicking on an icon in the system tray". This article shows how to achieve that, as well as providing the same OF…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question