3

I have a column in SQL Server that is varchar(MAX). The column has been populated with that data type. I now want to convert the column to nvarchar(MAX) to accept unicode characters.

Is there any data loss when I convert this column from varchar(MAX) to nvachhar(MAX)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leslie Jones
  • 540
  • 2
  • 9
  • 22

1 Answers1

6

Every character that can be represented in varchar is also representable in nvarchar.

The only possible data loss issue would be if you have any varchar(max) values that are already greater than 1GB.

In most collations nvarchar uses 2 bytes per character and varchar uses 1 byte per character. Doubling the string length of a string > 1 GB would obviously take you over the 2GB limit.

Even then I would expect an error message rather than silent truncation but possibly it might silently fail under some settings such as ANSI_WARNINGS OFF.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • So just to clarify your saying the only possible error is if one of string value in the table is greater then 1gb in size? – Leslie Jones Sep 04 '15 at 21:24
  • Yes. You might get other errors of course such as out of disc space but this won't cause data loss. It would be rolled back. – Martin Smith Sep 04 '15 at 21:26
  • I see, Im most worried about truncation issues, In sql server I went to design view changed the datatype from varchar to nvarchar and it recreated the table. Didnt seem to get errors? Is that the right way to change the datatype? – Leslie Jones Sep 04 '15 at 21:29
  • I don't tend to use the GUI for this sort of change as it often ends up rebuilding the whole table unnecessarily. An `ALTER TABLE ... ALTER COLUMN` is often faster. – Martin Smith Sep 04 '15 at 21:32
  • Yes that makes sense I think i will use the alter statement from here on in thanks for the advice :) – Leslie Jones Sep 04 '15 at 21:45