nvarchar and different collation comparison

Hi,
I have read some articles on Unicode characters, I understand that they use
as a "big international code page" that host thousands of characters. The
thing I don't understand is why I get an error when I compare two nvarchar
column having a different collation... as this:
I know that COLLATE in the comparison would correct the issue, but I want to
find a way to avoid hard coding COLLATE keywords and avoid to change both
collations of the fields. I though nvarchar was to resolve this. Someone can
explain why it doesn't do that?
Or please leave me a personnal mail or web link... Thanks
CREATE TABLE a
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM a WHERE LatinAS = LatinAI
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
david.parenteau@.compuware.com
TIANVARCHAR allows you to use Unicode characters. There are still differences
in character sets when you move from collation to collation. So, your
options here are pretty limited.
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||David
CREATE TABLE b
(
LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
)
GO
INSERT INTO b (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
SELECT * FROM b WHERE LatinAS COLLATE Latin1_General_CI_AI = LatinAI
"David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
> Hi,
> I have read some articles on Unicode characters, I understand that they
> use
> as a "big international code page" that host thousands of characters. The
> thing I don't understand is why I get an error when I compare two nvarchar
> column having a different collation... as this:
> I know that COLLATE in the comparison would correct the issue, but I want
> to
> find a way to avoid hard coding COLLATE keywords and avoid to change both
> collations of the fields. I though nvarchar was to resolve this. Someone
> can
> explain why it doesn't do that?
> Or please leave me a personnal mail or web link... Thanks
>
> CREATE TABLE a
> (
> LatinAS nvarchar(50) COLLATE Latin1_General_CI_AS,
> LatinAI nvarchar(50) COLLATE Latin1_General_CI_AI
> )
> GO
> INSERT INTO a (LatinAS, LatinAI) VALUES ('Eric', 'Eric')
> SELECT * FROM a WHERE LatinAS = LatinAI
>
> Server: Msg 446, Level 16, State 9, Line 1
> Cannot resolve collation conflict for equal to operation.
>
> david.parenteau@.compuware.com
> TIA|||If nvarchar keep the same characters bytes for every characters in a
collation A, does the same character in collation B will be the same bytes
values?
Collation A
Characters A correspond to 265 for example.
In Collation B, does the character A will have the same bits and be 265 or
it could be something else?
Does the sort order and other things (except code page) in collations differ
also for nvarchar columns?
David
"Aaron Bertrand [SQL Server MVP]" wrote:

> NVARCHAR allows you to use Unicode characters. There are still difference
s
> in character sets when you move from collation to collation. So, your
> options here are pretty limited.
>
>
> "David Parenteau" <DavidParenteau@.discussions.microsoft.com> wrote in
> message news:5031A14F-4D36-47B8-A07F-3ABB5BF019D2@.microsoft.com...
>
>