Help! SQL Collation

[Sunit Joshi]

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_n o] [int] NULL ,
[lineid_1] [varchar] (16) COLLATE SQL_Latin1_Gene ral_CP1_CS_AS NULL ,
[iso_type] [smallint] NULL
) ON [PRIMARY]

I have a nonclustered, UNIQUE index located on PRIMARY on
iso_area_indx_n o, 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@netz ero.net

[Erland Sommarskog]

Re: Help! SQL Collation

Sunit Joshi (sjoshi@ingr.co m) writes:[color=blue]
> I have a table:
> CREATE TABLE [PD_RUSS3].[pdtable_185]
> (
> [iso_area_indx_n o] [int] NULL ,
> [lineid_1] [varchar] (16) COLLATE SQL_Latin1_Gene ral_CP1_CS_AS NULL ,
> [iso_type] [smallint] NULL
> ) ON [PRIMARY]
>
> I have a nonclustered, UNIQUE index located on PRIMARY on
> iso_area_indx_n o, 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..??[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp