Why I prefer nvarchar, nchar and ntext over varchar, char and text fieldtypes

When designing SQL-server databases I like to use fields starting with an "N".

The "N" stands for NATIONAL, which overhere in Europe we have a lot of, umlauts, trema's, accents... you name it, we love it!

We use a lot of unicode data, things that VARCHAR has difficulties handling, but that's not the main reason I like to use the "N" fields.

The reason itself is that fields without the "N" are padded with spaces to meet their maximum when you output them. This causes a lot of problems when trying to create relations or output them to fields.

For example, an NVARCHAR at length 20 with content "BH 90210" outputs this way:

where VARCHAR at length 20

Where the value is padded with 12 more spaces to be used with VARCHAR...

Although we in Europe are aware of this when designing SQL databases, I still get a lot of SQL-databases that use the VARCHAR, TEXT or CHAR fields which are a nightmare to use in CF, because of the way you creating lookups and relations sometimes. I have "TRIM()" on speed dial here to create good content.

Another SQL trick I use quite a lot, which does solve most problems is:

It's good design to not use the "N", since an NCHAR takes twice the byte-space of a CHAR, but considering the above, please use "N" when creating SQL databases.