Data write to unicode file change order randomly

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
LVL 1
wagachchiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Do you have an ORDER BY clause on the query that exports the data out of SQL Server?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
k_murli_krishnaCommented:
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
Anthony PerkinsCommented:
>>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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

wagachchiAuthor Commented:
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
k_murli_krishnaCommented:
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
Anthony PerkinsCommented:
>>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
wagachchiAuthor Commented:
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
Anthony PerkinsCommented:
>>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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.