August 3, 2010 at 12:05 pm
Hi,
I have following query, which I am using to troubleshoot the bad data..
select top 5 column1, *
from table1
order by column1
This gives me the column1 (which is nvarchar) with . value in one server, which helped me to identify the bad data.
But, If try the same query in another test server with the same data, it gives me column1 with null values first. I want to see the . value in both servers.
Thanks,
VG
August 3, 2010 at 12:17 pm
Are you sure the data is the same between the servers? ORDER BY should work the same way, regardless of NULL values. Maybe the databases have different collation settings? That might cause order to be different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2010 at 12:34 pm
I cheked the collation and it is the same..I am going to reload the data and test this..
Thanks for the response..
-VG
August 3, 2010 at 12:39 pm
It is possible, just thinking about it, for the NULL values to mess you up though. An ORDER BY statement that is working on NULL values could get those values in different order, assuming a single column, since the NULL values are all the same, it would order them in the manner they were retrieved, which could be different between databases, especiallly since you're talking about loading data rather than working from a backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2010 at 12:46 pm
The patented "is it plugged in question" :
Are you sure that the datasets are 100% identical on both servers BEFORE applying the order by?
With a linked server you could do select * form systemA EXCEPT select * from systemB and them B except A. Both excepts should return nothing if the data is really identical.
From there I'd look and both execution plans to get more ideas about what is hapenning differently between the machines.
I'd also try duplicating the data back from the 2nd server to the first server and repeat both steps. If you get the same results then, but not before, you know you have a setting to worry about... or maybe different sql server levels but that would really surprise me for this particular case.
Also can you psot the query, maybe we can spot something we can't see in this dumbed down version?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply