FIX: SELECT DISTINCT Returns Incorrect Results in SQL_Latin1_General_Pref_Cp1_CI

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