FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs (899976)

MORE INFORMATION

Steps to reproduce the problem

The following scripts reproduce the problem.

Scenario 1

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [t]
go
create table t (col numeric(10, 0))
go
insert t values (1)
go
select * from t where col = 1.1	
go
-- Returns zero rows. This is the correct result.
create index it on t(col)
go
select * from t where col = 1.1
go
-- Returns one row in SQL Server 2000 SP3 or in earlier builds. This is the incorrect result.

Scenario 2

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(N'[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [t1]
go
if exists (select * from dbo.sysobjects where id = object_id(N'[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [t2]
go
create table t1 (c1 numeric (10,0))
go
create table t2 (c1 numeric (11,1))
go
insert into t1 values (1.0)
insert into t2 values (1.1)
go
select t1.c1 from t2 inner loop join t1 on t2.c1 = t1.c1 option (force order)
go
-- Without an index, this query returns zero rows. This is the correct result.
create index indx on t1(c1)
go
select t1.c1 from t2 inner loop join t1 on t2.c1 = t1.c1 option (force order)
-- With an index on t1(c1), this query returns one row in SQL Server 2000 SP3 or in earlier builds. This is the incorrect result.

Performance difference

After you install SQL Server 2000 Service Pack 4 (SP4), SQL Server may not choose an index seek if you compare numeric columns or numeric constants that are of different precision or scale. Queries of this kind may run significantly slower in SQL Server 2000 SP4 than in SQL Server 2000 SP3. We recommend that you modify queries or schema so that the data type, the precision, and the scale are the same when comparisons are performed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Trace flag to revert to the behavior in SQL Server 2000 SP3

This problem may cause incorrect results because the SQL Server optimizer may convert from a higher precision or scale to a lower precision or scale to do an index seek. This conversion may cause data loss or truncation that leads to incorrect results. This conversion does not always cause data loss or incorrect results depending on the data that is stored in a table or depending on the values passed. If you are sure that this conversion will not affect your query results and if you cannot promptly rewrite the query, you can enable trace flag 9059 to revert to the behavior in SQL Server 2000 SP3. This could lead to incorrect results under certain conditions.

Additionally, you can set the database compatibility level to 70. For more information, click the following article number to view the article in the Microsoft Knowledge Base: Enabling trace flag 9059 only affects this specific behavior. Setting the database compatibility to 70 affects other SQL Server 2000 features such as your ability to use index views.

Trace flag 9059 is a dynamic trace flag. To enable trace flag 9059, use one of the following methods.

Method 1

In SQL Query Analyzer, use the following code.

DBCC TRACEON(9059 , -1) 

Note When you use this method, you must run the command again every time that you restart SQL Server.

Method 2

In SQL Server Enterprise Manager, add the -T9059 startup parameter to SQL Server. To do this, follow these steps:

1. In SQL Server Enterprise Manager, expand Microsoft SQL Servers, expand SQL Server Group, right-click the computer that is running SQL Server, and then click Properties.
2. On the General tab, click Startup Parameters.
3. In the Parameter box, type -T9059, and then click Add.
4. Click OK two times, and then close SQL Server Enterprise Manager.
5. Restart the computer that is running SQL Server.