Change Datatype Varchar to Nvarchar in Existing SQL Server 2005 Database. Any Issues

Change datatype varchar to nvarchar in existing SQL Server 2005 database. Any issues?

Note that this change is a size-of-data update, see SQL Server table columns under the hood. The change will add a new NVARCHAR column, it will update each row copying the dta from the old VARCHAR to the new NVARCHAR column, and then it will mark the old VARCHAR column as dropped. IF the table is large, this will generate a large log, so be prepared for it. After the update, run DBCC CLEANTABLE to reclaim the space used by the former VARCHAR column. If you can afford it , better run ALTER TABLE ... REBUILD, which will not only reclaim the space it will also completely remove physical deleted VARCHAR column. The linked article at the beginning has more details.

You may also be interested in enabling Unicode Compression for your table.

Change data type from varchar to nvarchar for a table which has a large amount of data

So, all what i did was,
1. Create a new table with nvarchar for required column
2. copy data from existing table to new table using ssis.
3. Create constraints and indexes on new table.
All these steps can be done when the app is still up and running
4. Rename existing table to _Old and rename new table to existing table's name
Above step hardly takes a second and is the only step when the app is down.

Like this i was able to reduce the app down time and successfully changed column data type.

SQL Server 2005: converting varchar to nvarchar issue

When you want to insert nvarchar literals into the database table, you must use the N'..' prefix.

So use

INSERT INTO dbo.YourTable(First_Name)
VALUES(N'йцукен')

so that this string will be treated as a unicode string

If you're not using the N'..' notation, you're really inserting a non-unicode string literal - and this will cause these conversions to ?

How to change every nvarchar column to varchar?

Here, to get you started:

Select 'Alter Table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] Alter Column [' + COLUMN_NAME + '] VarChar(' + CAST(CHARACTER_MAXIMUM_LENGTH As VARCHAR) + ')'
From INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'NVARCHAR'

This will generate all the needed alter statements for you (cut, paste, run).

Note that this does not take any constraints into account.

SQL: How to alter all varchar column in all tables to nvarchar?

Where should be the conversion done: on the old SQL, or after moving it on Azure SQL Server?

I would do it on local SQLServer First,Running this on Azure database,might cause you to run into some issues like hitting your DTU limits,disk IO throttling..

Are the dropped varchar columns packed into the backup table, or does the backup/restore also remove the dropped columns?

The space wont be released back to filesystem,also backup doesn't process free spaces,so you will not see much change there.You might want to read more on dbcc cleantable though,before proceeding ..

Can the process be somehow automated using a universal SQL script? Or is it necessary to write the script for each individual table?

It can be automated,may be you can use dynamic sql to see the column type and process further.You will also have to see if any of those columns are part of indexes,if so you have to drop them first

SQL Server - script to update database columns from varchar to nvarchar if not already nvarchar

The following query should get you what you need:

IF EXISTS 
  (SELECT *
   FROM sysobjects syo
   JOIN syscolumns syc ON
     syc.id = syo.id
   JOIN systypes syt ON
     syt.xtype = syc.xtype
   WHERE 
     syt.name = 'nvarchar' AND
     syo.name = 'MY TABLE NAME' AND
     syc.name = 'MY COLUMN NAME')
BEGIN
   ALTER ...
END

How do you change the datatype of a column in SQL Server?

ALTER TABLE TableName 
ALTER COLUMN ColumnName NVARCHAR(200) [NULL | NOT NULL]

EDIT
As noted NULL/NOT NULL should have been specified, see Rob's answer as well.

Are there any disadvantages to always using nvarchar(MAX)?

Same question was asked on MSDN Forums:

• Varchar(max) vs Varchar(255)

From the original post (much more information there):

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

---

Related Topics