Receiving a Warning "Column will be truncated"

RussSiegel

Hi,
I am using SQL Compare version 14.2.16.16006.
I modified a table, changing the datatype of a column from varchar(max) to nvarchar(max).
When I go to deploy, I get the following warning:
Column will be truncated. This deployment truncates the column [xxx] on table [xxxx]. Data may be lost unless additional steps are taken to preserve it.

Will changing from varchar(max) to nvarchar(max) result in data loss? How can I work around this. The field needs to be changed to nvarchar(max).

Any help is appreciated.

Thank you,
-Russ

BrainDonor

Yes, that is essentially it. The 'max' value is 2GB, regardless of the data type. 

BrainDonor

Don't forget that a varchar column uses 1 byte per character and nvarchar uses 2 bytes per character (not strictly accurate, but close enough). So, if you're moving 400 bytes of data from a varchar column to an nvarchar column, it will now take 800 bytes.
The software is warning you of this, because if you have any data that is taking more than 50% of the capacity of a char(max) column, it will be doubled in size and therefore be too large for a nvarchar(max) column.
You need to find the maximum length of your char(max) entries, before deciding to either ignore the warning or do something about the data.

RussSiegel

Thank you, BrainDoor.
As far as I can tell the character limit for nvarchar(max) is about 1 billion characters (as opposed to varchar(max) which is about 2 billion. Therefore, as long as none of my data is over the 1 billion limit of the nvarchar(max), I can ignore the warning. Is that correct?

Thanks again,
-Russ

RussSiegel

Thank you for the help.