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...
>
>

nvarchar = unicode?

Dear all,
I would like to ask the question as the tile. will all data store in
nvarchar type be converted to unicode? Thank you for all of your help.
Alex Yung
Hi Alex,
Thanks for your post.
I think the answer from Peter was short but rather solid. I just wanted to
post a quick note to see if you would like additional assistance or
information regarding this particular issue. We appreciate your patience
and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

nvarchar = unicode?

Dear all,
I would like to ask the question as the tile. will all data store in
nvarchar type be converted to unicode? Thank you for all of your help.
Alex YungHi Alex,
Thanks for your post.
I think the answer from Peter was short but rather solid. I just wanted to
post a quick note to see if you would like additional assistance or
information regarding this particular issue. We appreciate your patience
and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

nvarchar = unicode?

Dear all,
I would like to ask the question as the tile. will all data store in
nvarchar type be converted to unicode? Thank you for all of your help.
Alex YungYes
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts_na-nop_9msy.asp
Peter
"Information is the oxygen of the modern age. It seeps
through the walls topped by barbed wire, it wafts across
the electrified borders.""
Ronald Reagan
>--Original Message--
>Dear all,
> I would like to ask the question as the tile. will all
data store in
>nvarchar type be converted to unicode? Thank you for all
of your help.
>Alex Yung
>
>.
>|||Hi Alex,
Thanks for your post.
I think the answer from Peter was short but rather solid. I just wanted to
post a quick note to see if you would like additional assistance or
information regarding this particular issue. We appreciate your patience
and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

nvarchar / varchar(MAX) question

Hi all,
I'm curious about the efficiency of the varchar datatype in general. I
understand the difference between varchar and nvarchar (unicode), and
I understand what the new nvarchar(max) is in sql 2005. What I don't
understand is exactly how varchar uses server resources. Here's an
example to explain my confusion.
Let's say I have a database with a column that is varchar(200). Let's
say that users cannot enter in data over 100 characters long due to a
restriction from a client web app. This means I could have used
varchar(100). Since varchar (unlike char) can grow and shrink as
needed, have I lost anything by using varchar(200) instead of
varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
performance/storage by using this data type if my data is not going to
be that large?
Thank you!
I would not use nvarchar(max) on an nvarchar(200) column. It adds 2 bytes
per row. If you have "large value types out of row" turned off, it should
act the same as normal nvarchar but there may be other internal factors.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<mattdaddym@.gmail.com> wrote in message
news:b61b9ed4-7e6d-48f0-a533-ae2d2879cb46@.e4g2000hsg.googlegroups.com...
> Hi all,
> I'm curious about the efficiency of the varchar datatype in general. I
> understand the difference between varchar and nvarchar (unicode), and
> I understand what the new nvarchar(max) is in sql 2005. What I don't
> understand is exactly how varchar uses server resources. Here's an
> example to explain my confusion.
> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
> Thank you!
|||> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)?
No. However, keep in mind that your client app is not the only way to get
data into that table! If you only want 100 characters, then enforce it by
using the correct data type (or at the very least a check constraint). You
can update both database and client app(s) later if you need to expand the
data type to accommodate more characters.

> Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
I would never use a MAX type unless I *knew* I was going to require capacity
beyond the 4K or 8K limit. By default these values are stored off-row, so
that can affect efficiency and I/O issues if the query has to go all over
the place to assemble rows. You can override this setting but, as Jason
points out, there may very well be other internal factors, in addition to
the fact that you can't index, etc.
|||MAX columns can be in indexes as INCLUDED columns.
Not that I'm recommending it, but I want to make sure we're clear here.
I do agree with what Aaron said.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
....
> I would never use a MAX type unless I *knew* I was going to require
> capacity beyond the 4K or 8K limit. By default these values are stored
> off-row, so that can affect efficiency and I/O issues if the query has to
> go all over the place to assemble rows. You can override this setting
> but, as Jason points out, there may very well be other internal factors,
> in addition to the fact that you can't index, etc.
>
|||Sounds good. Thank you everyone.
On Feb 4, 5:45Xpm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> MAX columns can be in indexes as INCLUDED columns.
> Not that I'm recommending it, but I want to make sure we're clear here.
> I do agree with what Aaron said.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "Aaron Bertrand [SQL Server MVP]" <ten...@.dnartreb.noraa> wrote in messagenews:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl. ..
> ...
>
>
>
> - Show quoted text -
|||Sure, I should have been more explicit, can't index in the traditional way
(e.g. CREATE INDEX foo ON table.column).

> MAX columns can be in indexes as INCLUDED columns.

nvarchar / varchar(MAX) question

Hi all,
I'm curious about the efficiency of the varchar datatype in general. I
understand the difference between varchar and nvarchar (unicode), and
I understand what the new nvarchar(max) is in sql 2005. What I don't
understand is exactly how varchar uses server resources. Here's an
example to explain my confusion.
Let's say I have a database with a column that is varchar(200). Let's
say that users cannot enter in data over 100 characters long due to a
restriction from a client web app. This means I could have used
varchar(100). Since varchar (unlike char) can grow and shrink as
needed, have I lost anything by using varchar(200) instead of
varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
performance/storage by using this data type if my data is not going to
be that large?
Thank you!I would not use nvarchar(max) on an nvarchar(200) column. It adds 2 bytes
per row. If you have "large value types out of row" turned off, it should
act the same as normal nvarchar but there may be other internal factors.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<mattdaddym@.gmail.com> wrote in message
news:b61b9ed4-7e6d-48f0-a533-ae2d2879cb46@.e4g2000hsg.googlegroups.com...
> Hi all,
> I'm curious about the efficiency of the varchar datatype in general. I
> understand the difference between varchar and nvarchar (unicode), and
> I understand what the new nvarchar(max) is in sql 2005. What I don't
> understand is exactly how varchar uses server resources. Here's an
> example to explain my confusion.
> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
> Thank you!|||> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)?
No. However, keep in mind that your client app is not the only way to get
data into that table! If you only want 100 characters, then enforce it by
using the correct data type (or at the very least a check constraint). You
can update both database and client app(s) later if you need to expand the
data type to accommodate more characters.
> Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
I would never use a MAX type unless I *knew* I was going to require capacity
beyond the 4K or 8K limit. By default these values are stored off-row, so
that can affect efficiency and I/O issues if the query has to go all over
the place to assemble rows. You can override this setting but, as Jason
points out, there may very well be other internal factors, in addition to
the fact that you can't index, etc.|||MAX columns can be in indexes as INCLUDED columns.
Not that I'm recommending it, but I want to make sure we're clear here.
I do agree with what Aaron said.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
...
> I would never use a MAX type unless I *knew* I was going to require
> capacity beyond the 4K or 8K limit. By default these values are stored
> off-row, so that can affect efficiency and I/O issues if the query has to
> go all over the place to assemble rows. You can override this setting
> but, as Jason points out, there may very well be other internal factors,
> in addition to the fact that you can't index, etc.
>|||Sounds good. Thank you everyone.
On Feb 4, 5:45=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> MAX columns can be in indexes as INCLUDED columns.
> Not that I'm recommending it, but I want to make sure we're clear here.
> I do agree with what Aaron said.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Aaron Bertrand [SQL Server MVP]" <ten...@.dnartreb.noraa> wrote in messag=enews:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
> ...
>
>
> > I would never use a MAX type unless I *knew* I was going to require
> > capacity beyond the 4K or 8K limit. =A0By default these values are store=d
> > off-row, so that can affect efficiency and I/O issues if the query has t=o
> > go all over the place to assemble rows. =A0You can override this setting=
> > but, as Jason points out, there may very well be other internal factors,=
> > in addition to the fact that you can't index, etc.- Hide quoted text -
> - Show quoted text -|||Sure, I should have been more explicit, can't index in the traditional way
(e.g. CREATE INDEX foo ON table.column).
> MAX columns can be in indexes as INCLUDED columns.sql