Compatibility Mode 70 in SQL Server 2008

Hi,
does anybody know if MS's going to deprecate "Compatibility Mode 70"
databases in SQL Server 2008?
With SQL Server 2005, MS already removed CM70 support from all their
Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
afraid they're gonna go one step further.
thx
AxelThere is no "SQL Server 7.0 (70)" item in the Compatibility Level list in
the database options in SQL SErver 2008 November CTP.
Ekrem nsoy
"Axel Bender" <axel_bender@.t-online.de> wrote in message
news:fj0jhm$7l0$00$1@.news.t-online.com...
> Hi,
> does anybody know if MS's going to deprecate "Compatibility Mode 70"
> databases in SQL Server 2008?
> With SQL Server 2005, MS already removed CM70 support from all their
> Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
> afraid they're gonna go one step further.
> thx
> Axel|||... and Books Online (ALTER DATABASE and sp_dbcmptlevel) only lists levels
80, 90 and 100.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:6CB644E8-4284-4BA8-86B2-CD764F70EF27@.microsoft.com...
> There is no "SQL Server 7.0 (70)" item in the Compatibility Level list in
the database options in
> SQL SErver 2008 November CTP.
> --
> Ekrem nsoy
>
> "Axel Bender" <axel_bender@.t-online.de> wrote in message news:fj0jhm$7l0$0
0$1@.news.t-online.com...
>

Compatibility Mode 70 in SQL Server 2008

Hi,
does anybody know if MS's going to deprecate "Compatibility Mode 70"
databases in SQL Server 2008?
With SQL Server 2005, MS already removed CM70 support from all their
Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
afraid they're gonna go one step further.
thx
AxelThere is no "SQL Server 7.0 (70)" item in the Compatibility Level list in
the database options in SQL SErver 2008 November CTP.
--
Ekrem Önsoy
"Axel Bender" <axel_bender@.t-online.de> wrote in message
news:fj0jhm$7l0$00$1@.news.t-online.com...
> Hi,
> does anybody know if MS's going to deprecate "Compatibility Mode 70"
> databases in SQL Server 2008?
> With SQL Server 2005, MS already removed CM70 support from all their
> Wizards (e.g. the Database Backup Wizard - which is bad enough), so I'm
> afraid they're gonna go one step further.
> thx
> Axel|||... and Books Online (ALTER DATABASE and sp_dbcmptlevel) only lists levels 80, 90 and 100.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:6CB644E8-4284-4BA8-86B2-CD764F70EF27@.microsoft.com...
> There is no "SQL Server 7.0 (70)" item in the Compatibility Level list in the database options in
> SQL SErver 2008 November CTP.
> --
> Ekrem Önsoy
>
> "Axel Bender" <axel_bender@.t-online.de> wrote in message news:fj0jhm$7l0$00$1@.news.t-online.com...
>> Hi,
>> does anybody know if MS's going to deprecate "Compatibility Mode 70" databases in SQL Server
>> 2008?
>> With SQL Server 2005, MS already removed CM70 support from all their Wizards (e.g. the Database
>> Backup Wizard - which is bad enough), so I'm afraid they're gonna go one step further.
>> thx
>> Axel
>

Compatibility Mode 70 databases in SP2

Hi,
Does anybody know if the creation of maintenance plans for Compatibility
Mode 70 databases will be possible with SQL Server 2005 SP2?
According to what I've heard so far, there is no chance to do that in
SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
the wizard). I consider this a severe shortcoming in the product, which
should be tackled by MS.
Any opinions/workarounds?Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if you
are relying on such software.
John
"Axel Bender" wrote:
> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>|||Thanks for the answer, John.
Although I generally agree with you in saying that a compatibility mode
should not be used on a long-term basis, I have to make some additional
points on that topic:
a) We have deployed a lot of CM 70 databases; changing the compatibility
mode for them would not break our application, but it would give the
users very slow response times in parts of the app (this is due to the
change MS made to the query optimizer when it comes to selecting
indexes). We know we have to make changes to the app, but frankly, we
simply cannot afford the time for this now.
b) Most of our customers are not able to write maintenance procedures
(nor are all of our supporters).
c) There is no common scheme that our users follow when it comes to
backing up and checking their databases.
d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
70 ones?
I think, that supporting CM 70 databases would not be a big deal for MS;
for us it would be.
Kind regards,
Axel|||Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:
> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility Mode 70 databases in SP2

Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if you
are relying on such software.
John
"Axel Bender" wrote:

> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>
Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:

> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility Mode 70 databases in SP2

Hi,
Does anybody know if the creation of maintenance plans for Compatibility
Mode 70 databases will be possible with SQL Server 2005 SP2?
According to what I've heard so far, there is no chance to do that in
SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
the wizard). I consider this a severe shortcoming in the product, which
should be tackled by MS.
Any opinions/workarounds?Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if yo
u
are relying on such software.
John
"Axel Bender" wrote:

> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>|||Thanks for the answer, John.
Although I generally agree with you in saying that a compatibility mode
should not be used on a long-term basis, I have to make some additional
points on that topic:
a) We have deployed a lot of CM 70 databases; changing the compatibility
mode for them would not break our application, but it would give the
users very slow response times in parts of the app (this is due to the
change MS made to the query optimizer when it comes to selecting
indexes). We know we have to make changes to the app, but frankly, we
simply cannot afford the time for this now.
b) Most of our customers are not able to write maintenance procedures
(nor are all of our supporters).
c) There is no common scheme that our users follow when it comes to
backing up and checking their databases.
d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
70 ones?
I think, that supporting CM 70 databases would not be a big deal for MS;
for us it would be.
Kind regards,
Axel|||Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:

> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility and Performance

Is there any performance hit when using a lower compatiblity mode? IE 70 instead of 90?

Compatibility level 70 means you are trying to SQL Server 7.0 mode? Is that what you really want to do? You obviously will not be able to use any SQL 2005 features.

Look for sp_dbcmptlevel in BOL and check out all the info.

|||

Yes we want the database to be in 7.0 mode. I understand the options. What I want to know is... is there a performance hit by using different compatiblity modes? Since SQL 2005 'performs' better than SQL 7 or SQL 2000, will we see any of that performance related to queries etc, or will the added overhead of the compatibility negate the performance improvements.

Also, what performance related issues are there to consider when using different modes, if any?

Thanks,

Raymond Laubert

MCDBA, MCITP:Administrator, MCT

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 actio
n
'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 beyo
nd
> 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 change
d?|||"SQLDeb" <SQLDeb@.discussions.microsoft.com> wrote in message
news:C6CFDE94-0F46-474B-A9AA-CF7B2ADB6181@.microsoft.com...
> "David Browne" wrote:
>
> 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...
> 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 st
ep
> will sort the rows."
> Programmer2: "Cool."
> David
>
> David
>
>

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:
>
> 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...
> 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
>
>

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
>
>