FIX: ORDER BY on "Numeric" Data Type May Return Incorrect Data (297063)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q297063
BUG #: 353744 (SHILOH_BUGS)

SYMPTOMS

When rows are sorted from a column that is a numeric or decimal data type with a scale greater than zero (0), incorrect results may display.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: 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 at the beginning of this article.

This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

Use this code to reproduce the problem: In Query Analyzer, execute the following queries: 
CREATE TABLE Temp_Table (Col1 NUMERIC(7, 0)  , Col2 numeric(7,1 ) ) 
GO
INSERT INTO Temp_Table VALUES (1,1.0) 
GO
SELECT Col1 FROM Temp_Table
GO
SELECT Col1 FROM Temp_Table WHERE Col1=Col2 ORDER BY Col2
GO
DROP TABLE Temp_Table
GO
The result for the SELECT statement with the WHERE and the ORDER BY clause, indicates a value of 10 for the Col1 column instead of the expected value of 1. The value of Col1 depends on the number in the scale of Col2. If you define the Col2 column as "NUMERIC(7,2)", then the result for Col1 is 100.
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbBug kbfix kbProgramming KB297063
©2003 Microsoft Corporation. All rights reserved.