Select the product you need help with

The behavior is different when comparing between columns and constants with different data types in SQL Server 7.0 and later versions of SQL Server

Article ID: 271566 - View products that this article applies to.

This article was previously published under Q271566

SYMPTOMS

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.

Note SQL Server 2005 and later versions of the program include improvements to handle numeric data type comparisons. Other than the varchar/nvarchar scenario, all other scenarios that are described in this article do not apply to SQL Server 2005 and later versions. However, Microsoft still recommends that you correctly match data types when you perform data comparisons and joins.

Back to the top | Give Feedback

WORKAROUND

Although this new behavior provides consistency among comparisons in SQL Server, it may pose backward compatibility issues for applications that rely on the old semantics. 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.

Back to the top | Give Feedback

MORE INFORMATION

Behavior changes in SQL Server 2000 regarding the comparison of columns and constants have several implications on the expected results as compared to previous releases, such as:

Query Results
Query Execution Plans, Index Selection, and Performance
Error Conditions

Query results

Consider the following scenario:

create table T1 (col1 int NOT NULL)
go
insert T1 values (1)
insert T1 values (2)
insert T1 values (3)
go

If you run the following query on SQL Server version 7.0 (or earlier) or on SQL Server 2000 with database compatibility level set to 70, you receive the following results:

select * from T1 where col1=2.3

col1        
----------- 
2

(1 row(s) affected)

If you run the same query on SQL Server 2000 using the default compatibility level (80), you receive the following results:

col1        
----------- 

(0 row(s) affected)

In the first case, 2.3 is converted to an int value (yielding 2 as a result) and the query is executed as "...where col1=2," returning one row because constants are always converted to the data type column.

In the second case, the constant 2.3 is identified as an approximate number that is higher in the data type hierarchy than the column declared as int. Therefore, the query is evaluated as "convert(decimal(2,1), col1)=2.3" and no rows are returned. If you want SQL Server 2000 to behave as earlier versions did, then rewrite the query as:

select * from T1 where col1=convert(int,2.3)

The same situation applies to other data types as well. For example, a comparison between a column of char data type and a Unicode constant might generate different results on SQL Server 7.0 and SQL Server 2000.

Query execution plans, index selection, and performance

As stated earlier, in some cases a column may be converted to another data type to perform the comparison depending on the data types being compared. This means that a query with a predicate such as "<column> = <literal>" is treated as "...convert<(other_data_type>, <column)> = <literal>" and the execution plan for such a variation might change significantly.

Consider the following scenario:

create table T3 (col1 char(10) NOT NULL)
go
insert T3 values ('a')
insert T3 values ('b')
insert T3 values ('c')
go
create clustered index CIT3 on T3(col1)

The following query on SQL Server version 7.0 or on SQL Server 2000 with a 70 database compatibility level is run by using an index seek for the particular value:

select * from T3 where col1=N'a'

  |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[T3].[CIT3]), SEEK:([T3].[col1]=Convert([@1])) ORDERED FORWARD)

Under compatibility level 80, SQL Server 2000 uses an index scan, which results in higher I/O, CPU usage, and run time.

|--Clustered Index Scan(OBJECT:([Northwind].[dbo].[T3].[CIT3]), WHERE:(Convert([T3].[col1])=[@1]))

Consider another scenario:

create table T1 
(col1 int NOT NULL) 
go 
insert T1 values (1) 
insert T1 values (2) 
insert T1 values (3) 
go 
create clustered index clustind on T1(col1)

The following query on SQL Server 7.0 or on SQL Server 2000 with a 70 database compatibility level gives us the query plan below.

Select * from T1 where col1 = 1
 
|--Clustered Index Seek(OBJECT:([master].[dbo].[T1].[clustind]), SEEK:([T1].[col1]=[@1]) ORDERED)

Under compatibility level 80, SQL Server 2000 converts the literal 1 to the same data type as the Col1 column. Therefore, it is converted to an int.

The assumed base type of the literal 1 is tinyint. The tinyint data type is the lowest in the data type precedence in the integer family.

This allows you to convert the literal to the data type of the column without a loss in precision.

Select * from T1 where col1 = 1
 
|--Clustered Index Seek(OBJECT:([pubs].[dbo].[T1].[clustind]), SEEK:([T1].[col1]=Convert([@1])) ORDERED FORWARD)

If the conversion happens between numeric data types, equality comparison is executed as a range seek followed by a comparison (of the returned range) with the constant. This has the negative consequence of disallowing the use of additional columns on composite indexes, as in the following scenario:

create table T4 (col1 int NOT NULL, col2 int NOT NULL)

insert T4 values (1,1)
insert T4 values (1,2)
insert T4 values (1,3)

create clustered index CIT4 on T4(col1, col2)

Running the following query with previous behavior yields an index seek on the two columns:

select * from T4 where col1=1.1 and col2=2

|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[T4].[CIT4]),
SEEK:([T4].[col1]=Convert([@1]) AND [T4].[col2]=Convert([@2])) ORDERED FORWARD)

Under SQL Server 2000 with an 80 compatibility level, the plan includes an index range seek on the first column only, followed by a nested loops join with the constant to find the matching rows. (Note that the complete plan is not shown.)

|--Clustered Index Seek(OBJECT:([Northwind].[dbo].[T4].[CIT4]),
SEEK:([T4].[col1] > [Expr1004] AND [T4].[col1] < [Expr1005]),
WHERE:([T4].[col2]=Convert([@2]) AND Convert([T4].[col1])=Convert([@1]))
ORDERED FORWARD)

Error conditions

The new comparison behavior may also allow queries that used to fail on previous versions. Consider the following scenario:

create table T5 (col1 tinyint NOT NULL)
go
insert T5 values (1)
insert T5 values (2)
go

If you run the following query, an error is raised under the previous behavior because SQL Server attempts to convert the constant 300 into a tinyint value:

select * from T5 where col1= 300

Result:
Server: Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 300.

Under SQL Server 2000 (set to a compatibility level 80), the query runs because the tinyint column is converted to a larger integer data type:

col1 
---- 

(0 row(s) affected)

Additional resources

To change the compatibility level of a database, use the sp_dbcmptlevel stored procedure. For more information, refer to the "sp_dbcmptlevel" and "Database Compatibility Level Option" topics in SQL Server Books Online.

For more information on data type precedence hierarchy, see the "Data Type Precedence" topic in SQL Server Books Online.

For more information on using literals on SQL Server, see the "Constants" topic in SQL Server Books Online.

Back to the top | Give Feedback