Im building a website that might have unicode characters in it the client hasn't specified, I want to use nvarchar as the datatype on sql server. Is there any downside to using nvarchar over varchar.
Why would anyone want to use varchar over nvarchar if nvarchar can hold more characters the varchar. Is the only downside the side to using nvarchar that the data would be greater in nvarchar then varchar?
Also can Nvarchar still store all the characters that varchar stores?
I think the (indirect) point you're making is that you'd almost always want to use it. In general it's best to start accepting unicode data from the outset, otherwise you end up with a legacy headache that you don't want. You'd be surprised what you can overlook in regard to expected input. And systems have a habit of becoming bigger than you expected, with the need to handle internationalised input. There are certain fields that you'll want to restrict. For example if you're storing domain names, then support across systems for multilingual characters is likely to still be unreliable (it's being worked on), so you'll want to restrict the input for things like that. In those cases you'll need to restrict the input at a higher level, for example through a regular expressions at the UI level, otherwise if you've declared a varchar field in the database you'll just end up with completely the wrong character being stored if a unicode character does manage to get through.
Space isn't an issue these days, however there are performance considerations, although scenarios are unlikely that would outweigh the need for scalability:
https://msdn.microsoft.com/en-us/library/ms189617.aspx
A critical point is that you need to explicitly specify that you're using UTF-8 consistently throughout your application layers for full international support.
Choosing data types is as much art as science. But when it comes down to it, the type you choose implies a constraint. For instance, I wouldn't choose nvarchar(50) to store an American ZIP code. So don't just pick nvarchar blindly because it's more permissive - that's not a feature! Choose nvarchar because you legitimately believe that the field will need to accommodate Unicode characters. Which is to say that it might not be for all of your columns.
As for your second question, nvarchar is a strict superset of varchar.
I always use varchar and cast as nvarchar in both my sql stored procedures / functions etc and my vb.net classes where necessary. That said all of my work is ‘National’ currently as opposed to International. But space is a big concern when you’re deploying to cloud based architectures like Azure, and you’re working with billions of rows, and thousands of columns. It gets expensive. Any way of limiting the amount of space you need to consume is a bonus in my line.
