What is the difference between varchar and nvarchar?

The difference between varchar and nvarchar is -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: