asked
varchar versus nvarchar
(SQL Server 2005 and later) If I have a column that will NEVER be required to hold any characters other than the 256 ANSI characters is there any reason why I'd make it NVARCHAR instead of VARCHAR? Are there performance or storage advantages to using VARCHAR instead of NVARCHAR?
Your main consideration will be around that column and how it will be used. Will it be joined in any way to another column? If so you'll want the datatypes to match to prevent an implicit conversion, table scan and poor performance. Likewise, if you'll be querying that column as a part of a where clause from a .NET application which passes in unicode data by default you'll want to match up the types. Just remember, nvarchar uses 2 bytes per character stored versus a single byte for varchar data. There's always storage space to consider.
You should see that reduction using compression yes. You do need to be aware that Unicode data compression is an Enterprise level feature which exists in 2008 R2 and 2012 only. When attempting to use compression you also need to take into account the CPU overhead associated with compressing and decompressing that data, as such it may not be a good choice for a highly volatile table.
"...nvarchar uses 2 bytes per character..." Does Unicode compression (
http://msdn.microsoft.com/en-us/library/ee240835(v=sql.105).aspx) reduce the disparity between varchar storage and nvarchar storage?
Some excellent points SirSQL. I hadn't even considered the impact of joins. Most parameter variables in WHERE clauses will be coming in from SSRS (which I believe sends in NVARCHAR strings). Then there's collation to consider! Trouble is, now I don't have a clear answer. Sometimes it might be better for the column to be NVARCHAR and sometimes it might be better to be VARCHAR.
8 People are following this question.
