You can implicitly convert to unitext from other character and binary
datatypes. You can explicitly convert from unitext to other datatypes, and vice
versa.
However, the conversion result is limited to the maximum length of the destination datatype. When
a
unitext value cannot fit the destination buffer on a Unicode character
boundary, data is truncated. If you have set
enable surrogate processing,
the
unitext value is never truncated in the middle of a surrogate pair of
values, which means that fewer bytes may be returned after the datatype conversion. For
example, if a
unitext column
ut in table
tb
stores the string “U+0041U+0042U+00c2” (U+0041 represents the Unicode character “A”), this
query returns the value “AB” if the server’s character set is UTF-8, because U+00C2 is
converted to 2-byte UTF-8 0xc382:
select convert(char(3), ut) from tb
Currently, the alter table modify command does not support
text, image, or unitext columns as the
modified column. To migrate from a text to a unitext
column, you must first use bcp out to copy the existing data out, create
a table with unitext columns, and then use bcp in to
place data into the new table. This migration path works only when you invoke
bcp with the -Jutf8 option.