Menu
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?
930
If you store character data that reflects multiple languages, always use Unicode data types (nchar, nvarchar, and ntext) instead of the non-Unicode data types (char, varchar, and text). Otherwise your sorting will go weird. "Unicode - (nchar, nvarchar, and ntext).
Today's development platforms or their operating systems support the Unicode character set. Therefore, In SQL Server, you should utilize NVARCHAR rather than VARCHAR. If you do use VARCHAR when Unicode support is present, then an encoding inconsistency will arise while communicating with the database.
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
As such, NVARCHAR(MAX) can take up to twice as much physical disk space as a NVARCHAR(1.. 4000) field with the same text content+ — even when not stored in the LOB. The non-SCSU waste depends on data and language represented.
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).
175
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
