question

GPO avatar image
GPO 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?
datatypesnvarcharvarchar
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
SirSQL avatar image
SirSQL answered
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.
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
3 Likes 3 ·
Your best bet would be to use SSRS to only execute stored procedures, that way you can strongly type the data with parameter usage.
1 Like 1 ·
"...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?
0 Likes 0 ·
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.
0 Likes 0 ·
So does that mean that even though SSRS may be passing in a unicode string, I can control that efficiently by declaring a variable within the stored proc that converts it to VARCHAR from the get-go? Have I understood you?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.