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

This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 356093 (SQL Server 8.0)
SYMPTOMS
In Microsoft SQL Server 2000 Service Pack 3 (SP3) and in earlier SQL Server 2000 service packs, a query that involves data that is the numeric data type may return incorrect results when the following conditions are true:
  • You compare numeric columns or numeric constants that are of different precision or scale.
  • You create an index on a numeric column, and an index seek is used in the query plan.
RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.This problem was corrected in SQL Server 2000 Service Pack 4.
MORE INFORMATION

Steps to reproduce the problem

The following scripts reproduce the problem.

Scenario 1

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

Scenario 2

use tempdbgoif exists (select * from dbo.sysobjects where id = object_id(N'[t1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)	drop table [t1]goif exists (select * from dbo.sysobjects where id = object_id(N'[t2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)	drop table [t2]gocreate table t1 (c1 numeric (10,0))gocreate table t2 (c1 numeric (11,1))goinsert into t1 values (1.0)insert into t2 values (1.1)goselect 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)goselect 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:
271566 SQL Server comparisons between columns and constants with different data types

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:
271566 SQL Server comparisons between columns and constants with different data types
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

  1. In SQL Query Analyzer, run the following statement.
    DBCC TRACEON(9059 , -1) 
  2. Run the following statement:
    DBCC FREEPROCCACHE
    After you run this statement, SQL Server clears the procedure cache. Additionally, SQL Server generates a new query plan.
  3. If you encounter this problem in a specific stored procedure, run the sp_recompilestored procedure against the specific stored procedure. Then, the specific stored procedure is recomplied the next time that the specific stored procedure runs.
Note When you use this method, you must run the following statement every time that you restart SQL Server:
DBCC TRACEON(9059 , -1) 

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 SQL Server.
Properties

Article ID: 899976 - Last Review: 01/17/2015 10:52:22 - Revision: 2.4

  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • kbnosurvey kbarchive kbtshoot kbprb KB899976
Feedback