What are the differences between sql server varchar vs.nvarchar ?
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. Please help me with sql server varchar vs.nvarchar. Thanks in advance!
SQL server varchar vs.nvarchar The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar. 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: