Is it safe to convert varchar and char into nvarchar and nchar in SQL Server?

Question

We currently have a number of columns in the database which are of type varchar. The application that uses them are in C# and it uses Linq2Sql for the communication (or what to call it).

We would like to support unicode characters, which means we would have to convert the varchar columns into nvarchar. Is this a safe operation? Is it just a matter of changing the column type and updating the dbml file, or are there more stuff that needs to be done? Any changes in the C# code? Do I need to somehow convert the text that already exist in the database manually, or is it handled for me?

Answer 1

It should be pretty safe to do from varchar/char to nvarchar/nchar. I have done it several times and have not experienced any problems. But this is based only on my experience!

Answer 2

Aside from a minor global performance impact, which shouldn't be relevant unless you can measure it, there can be performance implications due to the query plans being different if you're suddenly sending incompatible types. But if you're using nvarchar across the board that shouldn't happen (and in general it's more likely to happen if you're sending varchars to something that's going to process it as nvarchar.

Answer 3

Also bear in mind that the maximum length for an nvarchar column is 4000, which is half of varchar's 8000.

Answer 4

Sometimes you'll be better off with nvarchar since L2S has a tendency to pass nvarchar params for varchar columns. Besides that, yes you will be using a bit more disk space db-side but that shouldn't be a problem nowadays.

As for converting, indexes and FK constraints (if any) on the columns involved will be affected so you need to script dropping and recreating those as necessary...

Answer 5

Probably not an issue, as I assume you intend to go from ASCII, ISO-8859-1 (or something similar) to UTF-8, but if not, make sure you know what process SQL Server uses to set up the correct encoding when you alter the table.