471,050 Members | 1,423 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

MS SQL 7 and 2000

Does anyone know of any differences between queries in SQL 7 and SQL
2000? I have a collegue that says there could be "subtle" differences
in the output, though I cannot find any documentation of this. Any
link to prove him wrong would be very helpful. Thanks.

Jul 23 '05 #1
3 1659
kingk436 (de*********@circuitmap.com) writes:
Does anyone know of any differences between queries in SQL 7 and SQL
2000? I have a collegue that says there could be "subtle" differences
in the output, though I cannot find any documentation of this. Any
link to prove him wrong would be very helpful. Thanks.


One "subtle" difference is the rule for auto-conversion of data types.
In SQL 2000, conversion is done from a type hierarchy, whereas SQL 7
tries to first convert a constant.

This can differences if you have things like:

SELECT * FROM tbl where varcharcol > 1

In SQL 7, the 1 will be converted to a string, where as in SQL 2000
varcharcol will be converted to an integer. (And thus the query will
fail if there are values in the column that cannot be converted to
integer.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
kingk436 (de*********@circuitmap.com) writes:
Does anyone know of any differences between queries in SQL 7 and SQL
2000? I have a collegue that says there could be "subtle" differences
in the output, though I cannot find any documentation of this. Any
link to prove him wrong would be very helpful. Thanks.
One "subtle" difference is the rule for auto-conversion of data types.
In SQL 2000, conversion is done from a type hierarchy, whereas SQL 7
tries to first convert a constant.

This can differences if you have things like:

SELECT * FROM tbl where varcharcol > 1

In SQL 7, the 1 will be converted to a string, where as in SQL 2000
varcharcol will be converted to an integer. (And thus the query will
fail if there are values in the column that cannot be converted to
integer.)


In addition I believe that without an order by, results returned will be
different. (Of course the order shouldn't be specified, but I believe both
SQL 7.0 and 2000 do default to some underlying physical order, but each
differently from the other.)



--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #3
Greg D. Moore (Strider) (mo****************@greenms.com) writes:
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
In SQL 7, the 1 will be converted to a string, where as in SQL 2000
varcharcol will be converted to an integer. (And thus the query will
fail if there are values in the column that cannot be converted to
integer.)


In addition I believe that without an order by, results returned will be
different. (Of course the order shouldn't be specified, but I believe both
SQL 7.0 and 2000 do default to some underlying physical order, but each
differently from the other.)


Well, without any ORDER BY you could get different results from the
same server running SQL 7 or later depending on the phase of the moon,
availble CPUs if there are parallel streams, etc.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Dagwood | last post: by
10 posts views Thread by noname | last post: by
2 posts views Thread by Scott | last post: by
5 posts views Thread by Mo | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.