Collation Question

[bangalibhai]

Hi,

I have the below in one of my stored procedures.

Declare @tmp table(OptionCode varchar(2) COLLATE SQL_Latin1_General_CP1_CS_AS PRIMARY KEY CLUSTERED)

Record #1: "TP"
Record #2: "Tp"

1st record is insrted in to the above table and as soon as the second record is added "Violation of PRIMARY KEY constraint" error is thrown. Any idea why it would do that even though I have the specific column specified as case sensitive.

Thanks in advance.

 

[gmmastros]

It works for me. It must be something else.

[COLOR=blue]Declare[/color] @tmp [COLOR=blue]table[/color](OptionCode [COLOR=blue]varchar[/color](2) [COLOR=blue]COLLATE[/color] SQL_Latin1_General_CP1_CS_AS [COLOR=blue]PRIMARY[/color] [COLOR=blue]KEY[/color] [COLOR=blue]CLUSTERED[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @tmp [COLOR=blue]Values[/color]([COLOR=red]'TP'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @tmp [COLOR=blue]Values[/color]([COLOR=red]'Tp'[/color])

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] @tmp

-George

"the screen with the little boxes in the window." - Moron

 

[ESquared]

This would make good material for a SQL teaser on altering a table's collation violating a primary key constraint...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]