wagachchi
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
I am afraid that is not correct: The GROUP BY clause alone does not guarantee any specific sort order.
ASKER
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.
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.
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.
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.
>>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.
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.
ASKER
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.
Further investigations found that, one record has unprintable character which causes the issue, once removed looks fine.
Thanks Guys.
>>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.
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.
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.