Converting data types to Unicode

Question

Converting data types to Unicode

Bhushan Kalla

Default port

Points: 1475
More actions
August 15, 2007 at 3:01 pm

#153751

Hi Everyone,
Introduction:
My company is expanding to International level and application would accept all kinds of international languages as user entered data and stored in database.

Issues: Now I have to change the user entered data fields to unicode data types and most of these fields are TEXT data type. Since most of these fields are text data type, I cannot use ALTER TABLE command (text data types cannot be altered to Ntext using alter command). So currently I am adding a new column with ntext to the table and updated the new field with text data type field. But this is locking the table for long time and I have approximately billion rows in each table and there are 6 table should be modified like this.
It would be great help any kind of your thought on this issue. Even it's a little and you think that be worth please share with me.

Bhushan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 1

Unfortunatly my best bet is whatever you do is going to take awhile. Also you are going to chew threw alot of space doing it. However one piece of advice I would give you is that if you are using SQL Server 2005 I would switch to nvarchar(max) rather than ntext. ntext is going to be removed in the future anyway and nvarchar(max) will let you do alot of things you couldn't do with text. You will probably need to review your code however to remove any "text" only functions.

Ken

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

bledu SSChampion Points: 12335 More actions · Answer 2

what about creating a separate db with the required nvarchar datatypes and then do an import into it

Everything you can imagine is real.

Bhushan Kalla Default port Points: 1475 More actions · Answer 3

thank you all folks. Its great idea. But how to change the text data type to nvarchar(MAX). I found something interesting and happy to share with you. When I use alter command (ALTER TABLE TABLE_NAME ALTER COLUMN COLUMN_NAME NVARCHAR(MAX)) it's converting regular text data type into ntext. it got me into new thing now how to alter a data type into varchar(max).