In SQL Server changing column varchar(255) nvarchar

Question

I am using SQL server 2008 express and some of our columns are defined as varchar(255). Should I convert these columns to NvarChar(255) or nvarchar(max)?

The reason I ask is I read that nvarchar(255) for unicode characters would actually store 1/2 the number of characters (since unicode characters are 2 bytes) whereas 255 with varchar() would allow me to store 255 characters (or is it 255 - 2 for the offset).

Would there be any performance hits using nvarchar(max)?

JDs

Answer 1

Well, not quite - converting to NVarChar(255) doesn't cut your number of characters being stored in half - it still stores 255 characters. It just needs twice as much space (510 bytes vs. 255 bytes).

You should convert to NVARCHAR - even though it uses twice as much space all the time - if you:

• need to support Arabic, Hebrew, Cyrillic, or any of the East Asian languages - only in Unicode will you be able to actually capture those characters
• need to support other languages which use the "standard" Latin alphabet, but have special characters - things like Eastern European (Slavic) languages with their characters like č ă ě - those will be stored as just c, a, e in a varchar() field

NVarchar(max) is a great option - if you really need up to 2 GB of text. Making all string fields nvarchar(max) just do be "consistent" is a really really bad idea - you'll have massive performance issues. See Remus Rusanu's article on the topic