I'm designing a database scheme right now and I figure just to be safe I should use nvarchar for my textual column's datatypes (for unicode support). While I don't expect non-english text I figure it would be better to have support it from the begining just in case.
Is there any reason why I should stick with plain varchar? Performance?
In today's i18n world, nvarchar makes a lot of sense. varchar might make sense for data that is specified not to be unicode (perhaps you have some system fields that are demanded to be ASCII).
I'd use nvarchar by default for things like names, descriptions, addresses, etc.
varchar is smaller, so there are perhaps some IO savings with varchar over nvarchar (but note that code-page becomes a bigger issue too).
Also, see this question: VARCHAR vs NVARCHAR performance.
Personally, I say stick to varchar (as I answered in this thread). There is a non-trivial performance overhead.
We use VARCHAR for almost everything, and NVARCHAR only very very occasionally.
Product Codes don't need NVarchar - we don't allow anything other than A-Z, 0-9 and "_" in them ...
Its twice the storage space, but also only have half the entries per index page (and per data page) and half the memory cache is "wasted", more CPU cycles to compare data, and so on.
IME the commonly used foreign accents work just find in Varchar (i.e. LATIN-1). We have no plans to do Chinese or other alternate character sets, and when we do being able to handle that characterset by having using NVarchar from Day One will be the least of our worries - Right-to-Left or Vertical alignment of text?? :(
And if you allowed NVarchar for, say, a Name how are you going to type the extended charcater in from your keyboard? And if you import the data (so it is already NVarchar) how are you going to be able to search for that customer using your standard QWERTY keyboard. Lots and lots involved with internationalising an application, so my view is that there is no point "allowing for it by using NVarchar".
But there again lots of places I go to have NVarchar ... and most of the columns are 50 characters wide too .... they must know something about population growth and expansion plans for ZIP codes that I don't!!
Yes, performance, in size. nvarchar takes more bytes, I think it's double (correct me if I'm wrong) and this is because of the unicode support. So if you don't need unicode support, go with regular varchar.
As mentioned already, the trade off is future-proofing versus performance. In my experience, SQL Server does fairly well with lower CPU and memory limitations, but give it a slow disk I/O and it can really chug.
If you have no plans for dual-byte character sets (i.e. Chinese characters), stick with VARCHAR(MAX).
Generally speaking; Start out with the most expensive datatype that has the least constraints. Put it in production. If performance starts to be an issue, find out what's actually being stored in those nvarchar columns. Is there any characters in there that wouldn't fit into varchar? If not, switch to varchar. Don't try to pre-optimize before you know where the pain is. My guess is that the choice between nvarchar/varchar is not what's going to slow down your application in the foreseable future. There will be other parts of the application where performance tuning will give you much more bang for the bucks.
