We help IT Professionals succeed at work.

Internationalisation varchar to nvarchar ?

plq
plq asked
on
588 Views
Last Modified: 2008-02-01
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
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Database Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
plq

Author

Commented:
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

Commented:
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

Author

Commented:
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

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
plq

Author

Commented:
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!)

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

Author

Commented:
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

Author

Commented:
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

Commented:
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

Author

Commented:
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
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.