Take the 2-minute tour ×
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.

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
    
See this thread, someone explain the difference: stackoverflow.com/questions/144283/… –  Turque Mar 6 '13 at 16:34
    
Other than the fact that nvarchar uses twice the space than varchar - no, no difference. –  marc_s Mar 6 '13 at 20:11
    
If you use data compression (the lightweight row compression is enough) you will usually find nchar and nvarchar take no more space than char and varchar, due to Unicode compression. –  Paul White Mar 9 '13 at 10:29

3 Answers 3

up vote 5 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
    
Wow, my strings show up as ugly boxes on a mobile device. Oops. –  Aaron Bertrand Mar 9 '13 at 12:02
    
Good answer, I like the example. –  Chris L Mar 26 '13 at 15:18

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.

share|improve this answer
    
Data compression will reduce or eliminate the storage difference, depending on how many double-byte characters are used. Presumably, though, they are changing to nvarchar because they need to, so I wouldn't be so cavalier about dropping the N prefix. You should always use it when dealing with Unicode strings instead of cherry picking. –  Aaron Bertrand Mar 9 '13 at 12:01
    
@AaronBertrand The important point is that they won't have to immediately change every varchar literal to an nvarchar literal to keep the application working, which helps ease the process. Also, interesting point about compression. I just confirmed with DBCC PAGE, and you're correct. Does it use UTF-8 internally instead of UTF-16? –  db2 Mar 11 '13 at 12:17
    
I don't know about the UTF, for some reason I think only UTF-16 and UCS-2 are supported internally, but I'd have to go read something to confirm and it's way too early for that. The danger of course with leaving the old parameters, literals etc. as varchar is that some will be forgotten, so it's good to attack as much as possible up front IMHO. –  Aaron Bertrand Mar 11 '13 at 13:26
    
Found it. Looks like they use SCSU, which is vaguely similar to UTF-8, but a different algorithm. Unicode Compression Implementation –  db2 Mar 11 '13 at 14:20

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

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.