Comparision test SQL 7.0 and 2000

I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
I'm in the process of doing a compatibility test on files generated from the
same database on SQL 2000 (which is also running in 6.5 compatibility mode -
I copied it from the 7.0 server using the 2000 copy database wizard). I'm
getting different results when I create a file against test data, and don't
understand why. The file is generated from a query. The differences in the
file seem to be due to how the DirActivityLog sorts when it is matched.
The query is:
declare @.start as datetime,
@.stop as datetime
Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
da.ActivityCode
from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
where dir.BatchRcvdDate between @.start and @.stop
and dir.CompleteFlag = 1 and
dir.DirCode *= da.DirCode and
dir.BatchRcvdDate *= da.ActivityDate
order by dir.DirCode, dir.HistID
The data contains 2 records in RDHistory..Directory for dircode 116004,
BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and action
'U", and the second with histid 35098 and action 'D'.
DirActivityLog contains 2 records for the same dircode 116004, with
ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563 and
ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
On SQL 7.0, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode fields:
116004,U,N
116004,U,D
116004,D,N
116004,D,D
On SQL 2000, I get the following 4 rows with this in the DirCode, ActionCode
and ActivityCode:
116004,U,N
116004,U,D
116004,D,D
116004,D,N
This changes the results written out to the end file.
The execution plans on both servers seem to be the same. The code page and
sortid used on SQL 7.0 seems compatibile with the collation used on SQL 2000.
I can solve this problem by adding more sort fields to the query, but I'd
rather not modify the application if I can help it - and I don't know why
this is occuring.
Help!SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:FD582374-795E-4AF6-902F-9C5F76F9561A@.microsoft.com...
>I have a 6.5 compatibility mode database that is running on SQL Server 7.0.
> I'm in the process of doing a compatibility test on files generated from
> the
> same database on SQL 2000 (which is also running in 6.5 compatibility
> mode -
> I copied it from the 7.0 server using the 2000 copy database wizard). I'm
> getting different results when I create a file against test data, and
> don't
> understand why. The file is generated from a query. The differences in
> the
> file seem to be due to how the DirActivityLog sorts when it is matched.
> The query is:
> declare @.start as datetime,
> @.stop as datetime
> Select @.start = '03/15/05 00:01:00', @.stop = '04/15/05 00:01:00'
> Select Dir.DirCode, Dir.PubCode, Dir.CountryCode,
> Dir.StateCode, Dir.DirNameFull, Dir.DirNameAbbr,
> Dir.FocusCode, Dir.SubCatCode, Dir.SectionFlag, dir.Action,
> da.ActivityCode
> from RDHistory..Directory dir (NOLOCK), DirActivityLog da (NOLOCK)
> where dir.BatchRcvdDate between @.start and @.stop
> and dir.CompleteFlag = 1 and
> dir.DirCode *= da.DirCode and
> dir.BatchRcvdDate *= da.ActivityDate
> order by dir.DirCode, dir.HistID
> The data contains 2 records in RDHistory..Directory for dircode 116004,
> BatchRcvdDate = '3/29/05 7:00:00 AM' - the first with histid 35097 and
> action
> 'U", and the second with histid 35098 and action 'D'.
> DirActivityLog contains 2 records for the same dircode 116004, with
> ActivityDate = '3/29/05 7:00:00 AM' - the first record with Logid 47563
> and
> ActivityCode = 'N' and the second with Logid 47564 and ActivityCode 'D'.
> On SQL 7.0, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode fields:
> 116004,U,N
> 116004,U,D
> 116004,D,N
> 116004,D,D
> On SQL 2000, I get the following 4 rows with this in the DirCode,
> ActionCode
> and ActivityCode:
> 116004,U,N
> 116004,U,D
> 116004,D,D
> 116004,D,N
>
This is expected. Your ORDER BY does not impose a complete ordering on the
results. The first two records have HistID=35097 and the second two have
HistID=35098. Beyond that, the order is unpredictable.
You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
But since multiple rows in the result can share the same (DirCode,HistID),
you have also told the server that you do not care about the ordering beyond
DirCode and HistID. Unordered rows are returned an a order which is an
accident of the implementation of the query execution. The order of these
rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
2000 to reproduce the order from 6.5.
You must decide how you want the results sorted, and force the sort order by
adding appropriate columns to the ORDER BY.
David|||"David Browne" wrote:
> You have asked the server to "order by dir.DirCode, dir.HistID", and it is.
> But since multiple rows in the result can share the same (DirCode,HistID),
> you have also told the server that you do not care about the ordering beyond
> DirCode and HistID. Unordered rows are returned an a order which is an
> accident of the implementation of the query execution. The order of these
> rows was never guaranteed in SQL 6.5, and so there is no flag to force SQL
> 2000 to reproduce the order from 6.5.
> You must decide how you want the results sorted, and force the sort order by
> adding appropriate columns to the ORDER BY.
> David
Thanks for your quick response. I understand your point, and I can
certainly modify the order by clause in the sql statement to enforce the
order I want. However, what I don't understand is why SQL Server 7.0 would
consistently implement the sql one way, and SQL Server 2000 would
consistently implement it in another. If I got random sort results on these
fields on both servers - that I would understand. Any idea of what's changed?|||"SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> "David Browne" wrote:
>> You have asked the server to "order by dir.DirCode, dir.HistID", and it
>> is.
>> But since multiple rows in the result can share the same
>> (DirCode,HistID),
>> you have also told the server that you do not care about the ordering
>> beyond
>> DirCode and HistID. Unordered rows are returned an a order which is an
>> accident of the implementation of the query execution. The order of these
>> rows was never guaranteed in SQL 6.5, and so there is no flag to force
>> SQL
>> 2000 to reproduce the order from 6.5.
>> You must decide how you want the results sorted, and force the sort order
>> by
>> adding appropriate columns to the ORDER BY.
>> David
> Thanks for your quick response. I understand your point, and I can
> certainly modify the order by clause in the sql statement to enforce the
> order I want. However, what I don't understand is why SQL Server 7.0
> would
> consistently implement the sql one way, and SQL Server 2000 would
> consistently implement it in another. If I got random sort results on
> these
> fields on both servers - that I would understand. Any idea of what's
> changed?
It's not random. It's an accident of the implementation. Sql 2000 uses
different file structures and extensively revised and optimized
implementations of sorts and joins and whatnot. It just so happens that
these implementations return unsorted rows in a different order than SQL
6.5.
Imagine this conversation:
Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
pushing the row pointers onto a temporary stack."
Programmer2: "But won't that reverse the order the rows are returned?"
Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
will sort the rows."
Programmer2: "Cool."
David
David|||You're right - it's a moot point. Thanks again.
"David Browne" wrote:
> "SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
> news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> > "David Browne" wrote:
> >
> >> You have asked the server to "order by dir.DirCode, dir.HistID", and it
> >> is.
> >> But since multiple rows in the result can share the same
> >> (DirCode,HistID),
> >> you have also told the server that you do not care about the ordering
> >> beyond
> >> DirCode and HistID. Unordered rows are returned an a order which is an
> >> accident of the implementation of the query execution. The order of these
> >> rows was never guaranteed in SQL 6.5, and so there is no flag to force
> >> SQL
> >> 2000 to reproduce the order from 6.5.
> >>
> >> You must decide how you want the results sorted, and force the sort order
> >> by
> >> adding appropriate columns to the ORDER BY.
> >>
> >> David
> >
> > Thanks for your quick response. I understand your point, and I can
> > certainly modify the order by clause in the sql statement to enforce the
> > order I want. However, what I don't understand is why SQL Server 7.0
> > would
> > consistently implement the sql one way, and SQL Server 2000 would
> > consistently implement it in another. If I got random sort results on
> > these
> > fields on both servers - that I would understand. Any idea of what's
> > changed?
> It's not random. It's an accident of the implementation. Sql 2000 uses
> different file structures and extensively revised and optimized
> implementations of sorts and joins and whatnot. It just so happens that
> these implementations return unsorted rows in a different order than SQL
> 6.5.
> Imagine this conversation:
> Programmer1: "Hey I can shave 0.05% from the CPU use for a hash join by
> pushing the row pointers onto a temporary stack."
> Programmer2: "But won't that reverse the order the rows are returned?"
> Programmer1: "Yes but if the order is important, a subsequent ORDER BY step
> will sort the rows."
> Programmer2: "Cool."
> David
>
> David
>
>