SQL Server 2000在查寻比较不同数据类型的列和常量时与早期版本的SQL Server 有所不同

SQL Server 2000 behavior differs from previous versions of SQL Server when queries that involve comparisons between columns and constants with different data types are run. You can expect results and performance differences because of the way data type conversions are determined compared to earlier releases of SQL Server.

In SQL Server versions 7.0 and earlier, whenever a query uses a comparison operator between a column and a literal, the data type of the column is used regardless of the precedence rules. That is, if the column and the literal have different data types, the latter is always converted to the data type of the column (as long as the conversion is valid). This behavior may lead to undesired precision reduction, string truncation, or other conversions.

In SQL Server 2000, this is no longer true. A conversion is performed according to data type precedence, as it is in any other comparison. In the case where a literal is positioned higher in the hierarchy, the comparison is made between the constant and the converted column (as opposed to previous versions) and therefore, results may differ. Consequently, existing indexes may not be useful, different execution plans may be chosen, and performance may be impacted negatively.

You can force SQL Server 2000 to behave in the same way as earlier versions of SQL Server by doing either of the following:

Explicitly cast the constants that need to be converted (to match the data type of a column).

-or-

Set a database compatibility-level of 70 by using sp_dbcmptlevel.

Compatibility levels should be viewed only as a configuration option designed to smooth the progress of a migration from earlier versions. The recommended way to obtain the conversion of a constant and not of a column is through an explicit cast, using the CONVERT or CAST functions.

NOTE: Queries that used to be run with efficient index seeks may now use index scans or table scans. Higher CPU time, execution times, or I/O may be evidence that you are being negatively affected by the new behavior. Microsoft strongly recommends that you use explicit casts in these cases.

NOTE: This behavior does not apply to the LIKE operator. It does apply to the comparison operators (equality, inequality, greater than, and so forth) IN and BETWEEN.

In conclusion, a query which would take 2 seconds in SQL 7.0 took 5 minutes in SQL 2000. After modifying the datatype, this same query now runs for one second.