FIX: SELECT DISTINCT Returns Incorrect Results in SQL_Latin1_General_Pref_Cp1_CI_AS Collation

Article translations Article translations
Article ID: 295045 - View products that this article applies to.
This article was previously published under Q295045
BUG #: 352092 (SHILOH_BUGS)
Expand all | Collapse all

SYMPTOMS

The collation SQL_Latin1_General_Pref_Cp1_CI_AS has an expected behavior of case insensitivity, where an "a" is collated the same as an "A". In certain SQL Server 2000 fixes, when you use SELECT DISTINCT to select data, uppercase and lowercase versions of the same data (such as "a" and "A") may both be returned.

This behavior is not observed in the release build of SQL Server 2000, but may be observed in some fixes.

RESOLUTION

To resolve this problem, obtain the latest service pack for 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 SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.

MORE INFORMATION

To test if the problem exists on the build that you are running, execute the following T-SQL code from Query Analyzer:
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t (col1 VARCHAR(10) COLLATE sql_latin1_general_pref_cp1_ci_as)
GO
INSERT t VALUES ('c')
INSERT t VALUES ('a')
INSERT t VALUES ('b')
INSERT t VALUES ('C')
INSERT t VALUES ('A')
INSERT t VALUES ('B')
GO
SELECT DISTINCT col1 FROM t
GO
				
The expected behavior is to return three rows, but the versions affected by this bug will return six rows.

Properties

Article ID: 295045 - Last Review: November 5, 2003 - Revision: 3.2
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbbug kbfix kbsqlserv2000sp1fix KB295045

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com