Write differences between varchar and nvarchar

You need to be sure that you prefix Unicode string literals with an N prefix. For example these will work differently if the underlying data type is NVARCHAR:

CREATE TABLE dbo.t(c NVARCHAR(32));

INSERT dbo.t(c) SELECT 'រៀន';
INSERT dbo.t(c) SELECT 'នរៀ';
INSERT dbo.t(c) SELECT N'រៀន';

SELECT c FROM dbo.t;

SELECT c FROM dbo.t WHERE c = 'រៀន';
SELECT c FROM dbo.t WHERE c = N'រៀន';

Results:

c
----
??? -- not stored correctly
??? -- not stored correctly
រៀន -- stored correctly!

c
----
???
??? -- probably not expected, however all Unicode characters have been changed to ?

c
----
រៀន

For those on mobile devices or decrepit browsers that show box characters instead of actual Unicode characters, this is what it looks like:

The biggest concern is that nvarchar uses 2 bytes per character, whereas varchar uses 1. Thus, nvarchar(4000) uses the same amount of storage space as varchar(8000)*.

In addition to all of your character data needing twice as much storage space, this also means:

• You may have to use shorter nvarchar columns to keep rows within the 8060 byte row limit/8000 byte character column limit.
• If you're using nvarchar(max) columns, they will be pushed off-row sooner than varchar(max) would.
• You may have to use shorter nvarchar columns to stay within the 900-byte index key limit (I don't know why you would want to use such a large index key, but you never know).

Besides that, working with nvarchar isn't much different, assuming your client software is built to handle Unicode. SQL Server will transparently upconvert a varchar to nvarchar, so you don't strictly need the N prefix for string literals unless you're using 2-byte (i.e. Unicode) characters in the literal. Be aware that casting nvarchar to varbinary yields different results than doing the same with varchar. The important point is that you won't have to immediately change every varchar literal to an nvarchar literal to keep the application working, which helps ease the process.

* If you use data compression (the lightweight row compression is enough, Enterprise Edition required before SQL Server 2016 SP1) you will usually find nchar and nvarchar take no more space than char and varchar, due to Unicode compression (using the SCSU algorithm).

Think the following are major differences:

1. Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use.
2. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1.
3. JOIN-ing a VARCHAR to NVARCHAR has a considerable performance hit.
4. Might need an N prefix when inserts data: INSERT dbo.t(c) SELECT N'ʤ ʥ ʦ ʧ ʨ';
5. Some experts recommends nvarchar always because: since all modern operating systems and development platforms use Unicode internally, using nvarchar rather than varchar, will avoid encoding conversions every time you read from or write to the database