asked on

Internationalisation varchar to nvarchar ?

I'm looking at upgrading a sql server database with approx 120 tables to support unicode.

Would this mean that every varchar field has to become an nvarchar ? And char to nchar ? If so, is there an easy way of doing this without writing an alter table for each field in the db ??

Any alternatives, advice or experience in internationalising a sql database would be appreciated

thanks

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plq

ASKER

I am sure that most fields would need to support that text since a japanese installation would have japanese in just about every field

So I guess I can generate an alter script from sysobjects / syscolumns in a few minutes work ? Any issues or gotchas there ?

thanks

ptjcb

The only gotcha I know is what aneeshattingal mentions - nvarchar is only half the size of varchar - so if you have varchar(8000) you can only have nvarchar(4000) because nvarchar uses two bytes instead of one.

plq

ASKER

So one possible area of concern is performance on searches for character data. All our IDs are numeric so we're ok there on joins.

Any idea or gut feeling on how nvarchar installations perform against varchar installations ?

Another area of concern is database size. If all the varchars take twice as much space thats a possible issue and might drive some users over the 2GB msde limit.

If you store an ascii string in a nvarchar field is sql server clever enough to store it as single byte so only half the data size is used ??

thanks

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

ptjcb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

plq

ASKER

Yes, definitely mixing Japanese and English. Some of the tables contain information from software title names. You might have info in there for a Korean PC, a Japanese PC, and an English PC

Is this related to mixing codepages ? (not something I really understand but I have a feeling I'm gonna have to learn it!)

ptjcb

Yes - you will have to learn all about collations. It is fun (just kidding). Which version of SQL Server?

plq

ASKER

Currently 2000 but we are going to support 2005 as well from now on.

I would need it to work on both. We don't have any sql 7 or earlier installations

plq

ASKER

So if I do this query

Select * from SoftwareTable where Title like 'Microsoft Office%'

Would I need to add COLLATE clauses to each side of the like to force the strings to match ? Even out of the same field ?

Thats scaring me. I've used collate when importing data or comparing the imported database to the source database, but using it in our core product sounds like a nasty change from a testing perspective

ptjcb

It is a nasty change. You want to find "Mixed Collation Environments" in BOL - I found these web sites with info:

http://doc.ddart.net/mssql/sql2000/html/architec/8_ar_da_8uer.htm

http://209.34.241.67/michkap/archive/2006/01/29/518777.aspx

plq

ASKER

Just one more question. Does varchar(2000) accomodate [half] or [the same number] of literal characters as nvarchar(2000) ?

Just want to know if my customer data is going to get chopped in half

thanks

Aneesh

both accommodate the same no of charecters, but nvarchar needs double space to store that needed by varchar