FIX: SELECT DISTINCT Returns Incorrect Results in SQL_Latin1_General_Pref_Cp1_CI_AS Collation
This article was previously published under Q295045
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 352092 (SHILOH_BUGS)
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 certainSQL 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.
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 theMicrosoft Knowledge Base:
290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack
Microsoft has confirmed that this is a problem in SQL Server 2000. This problem was first corrected in SQL Server 2000 Service Pack 1.
To test if the problem exists on the build that you are running, execute the following T-SQL code from Query Analyzer:
The expected behavior is to return three rows, but the versions affected by this bug will return six rows.
SET NOCOUNT ONUSE tempdbGOIF OBJECT_ID('t') IS NOT NULL DROP TABLE tGOCREATE TABLE t (col1 VARCHAR(10) COLLATE sql_latin1_general_pref_cp1_ci_as)GOINSERT t VALUES ('c')INSERT t VALUES ('a')INSERT t VALUES ('b')INSERT t VALUES ('C')INSERT t VALUES ('A')INSERT t VALUES ('B')GOSELECT DISTINCT col1 FROM tGO
Article ID: 295045 - Last Review: 01/16/2015 22:17:50 - Revision: 3.2
Microsoft SQL Server 2000 Standard Edition
- kbnosurvey kbarchive kbbug kbfix kbsqlserv2000sp1fix KB295045