Order by ignored after upgrade from SQL Server 2000 to 2005

[Helmut Herrmann]

We have several Access 2003 projects (.adp) with data, queries, stored
procedures etc. in SQL Server. After upgrading SQL Server from 2000 to 2005
all queries do no longer sort their results according to the "order by"
clauses. Example:

SELECT TOP 100 PERCENT dbo.t_PERSONEN.PersID, dbo.t_FIRMA.FIRMA_NAME_KURZ,
dbo.t_PERSONEN.NACHNAME, dbo.t_FIRMA.FIRMA_ID
FROM dbo.t_FIRMA INNER JOIN
dbo.t_PERSONEN ON dbo.t_FIRMA.FIRMA_ID =
dbo.t_PERSONEN.FIRMA_ID
ORDER BY dbo.t_FIRMA.FIRMA_NAME_KURZ, dbo.t_PERSONEN.NACHNAME

I also opened the projects and tried the queries in Access 2007 and
experienced the same behavior.

Does anybody know a solution?

 

[Stefan Hoffmann]

hi Helmut,

We have several Access 2003 projects (.adp) with data, queries, stored
procedures etc. in SQL Server. After upgrading SQL Server from 2000 to 2005
all queries do no longer sort their results according to the "order by"
clauses.

This behaviour was corrected in SQL 2005, because from the SQL point of
view it was a wrong behavior. The optimzier in SQL 2005 is a lot more
aggressive and removes unnecessary ORDER BYs.

Does anybody know a solution?

Use a order by in your SELECTs, e.g. SELECT * FROM view ORDER BY ...

Or take a look at

http://support.microsoft.com/?scid=kb;en-us;926292&x=14&y=20



mfG
--> stefan <--

 

[Helmut Herrmann]

Hello Stefan,

Thank you very much for your reply.

This behaviour was corrected in SQL 2005, because from the SQL point of
view it was a wrong behavior. The optimzier in SQL 2005 is a lot more
aggressive and removes unnecessary ORDER BYs.

Use a order by in your SELECTs, e.g. SELECT * FROM view ORDER BY ...

Or take a look at

http://support.microsoft.com/?scid=kb;en-us;926292&x=14&y=20

However, I am still a little bit confused. If the behavior of SQL Server
2000 was wrong, why does Microsoft supply a hotfix to "correct" what is not
wrong in SQL Server 2005?

Regards,
Helmut

 

[Stefan Hoffmann]

2MSFq‰
\0\0\0\0\0hx.gbl\0 Herrmann wrote:Q‰
\0\0\0\0\0er, I am still a little bit confused. If the behavior of SQL Server

2000 was wrong, why does Microsoft supply a hotfix to "correct" what is not
wrong in SQL Server 2005?

To make it possible to migrate existing 200 installations to 2005
without complete function loss. To give the developer time to correct
their software...

imho this hotfix should not be considered a "solution" at all.


mfG
--> stefan <--