Help! SQL Collation

Hello All
I'm a bit confused about collation settings so needed some info. on
it. My database server is currently using SortOrder as

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
Sort Order 52 on Code Page 1252 for non-Unicode Data

I have a table:
CREATE TABLE [PD_RUSS3].[pdtable_185]
(
[iso_area_indx_no] [int] NULL ,
[lineid_1] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[iso_type] [smallint] NULL
) ON [PRIMARY]

I have a nonclustered, UNIQUE index located on PRIMARY on
iso_area_indx_no, lineid_1, iso_type

However I'm able to load a file with these entries:
45 '16-XX-WCS' 1
45 '16-xx-WCS' 1

I thought that this would fail because of the above settings. Please
enlighten me on what I'm missing here..??

thanks
Sunit
sunitjoshi@.netzero.netSunit Joshi (sjoshi@.ingr.com) writes:
> I have a table:
> CREATE TABLE [PD_RUSS3].[pdtable_185]
> (
> [iso_area_indx_no] [int] NULL ,
> [lineid_1] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [iso_type] [smallint] NULL
> ) ON [PRIMARY]
> I have a nonclustered, UNIQUE index located on PRIMARY on
> iso_area_indx_no, lineid_1, iso_type
> However I'm able to load a file with these entries:
> 45 '16-XX-WCS' 1
> 45 '16-xx-WCS' 1
> I thought that this would fail because of the above settings. Please
> enlighten me on what I'm missing here..??

You have a case-insensitive collation, that is xx, xX, Xx and XX are all
different. Thus, the two entries above should definitely both be loaded.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql