Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Currently in our SQL Server 2012 database, we're using varchar, and we'd like to change that nvarchar. I've generated a script to do that.

My question is are there any differences in how SQL Server writes to varchar columns vs. nvarchar columns? We have a number of backend procedures that I'm concerned about.

Edit:
Not sure if this helps, but the columns don't have indexes, f/k, or constraints on them.

share|improve this question
up vote 10 down vote accepted

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:

enter image description here

share|improve this answer

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) you will usually find nchar and nvarchar take no more space than char and varchar, due to Unicode compression (using the SCSU algorithm).

share|improve this answer

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
share|improve this answer

As far as I know, there is no difference between the way they are written, apart from that nvarchar uses 2 bytes of storage for every character stored rather than the single byte with varchar.

share|improve this answer

nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. It is a variable length data type. http://qualitypointtech.net/forum/viewtopic.php?f=14&t=8406

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.