Hello, within SQL Server 2019 there is a Column within a Table currently set as varchar(1000). When data is loaded into this Column we noticed it sometimes contains special characters which in one particular record for this Column had 1000 characters but size was actually slightly larger around 1040 Bytes (not having the 1 to 1 character to byte ratio). Realize probably will need to use some type of Unicode method. Will simply changing that Column to nvarchar(max) do it without existing data loss? Or would leaving this Column as is varchar(1000) and changing/implementing its Collation be better? Like mentioned definitely want to avoid data loss. Thanks in advance.

Yes, you can alter your column from being varchar(1000) to be nvarchar(MAX) without data loss. Beware, though, that this is a size-of-data operation, so it can take some time if the table is big. You could also keep it as varchar but change the collation a UTF8 collation. Or may be you already have a UTF8 collation, since you talk about 1-to-1 character byte ratio? If that is the case, there is no reason to change collation or data type, since you already have full Unicode support. But you may want to increase the max length or make it varchar(MAX) in that case.

Accommodate Column that contains special characters without existing data loss ?

Accepted answer

Erland Sommarskog 109.6K Reputation points MVP

2024-02-06T22:10:20.93+00:00

Yes, you can alter your column from being varchar(1000) to be nvarchar(MAX) without data loss. Beware, though, that this is a size-of-data operation, so it can take some time if the table is big.

You could also keep it as varchar but change the collation a UTF8 collation. Or may be you already have a UTF8 collation, since you talk about 1-to-1 character byte ratio? If that is the case, there is no reason to change collation or data type, since you already have full Unicode support. But you may want to increase the max length or make it varchar(MAX) in that case.
Please sign in to rate this answer.
techresearch7777777 1,841 Reputation points

2024-02-06T22:33:24.14+00:00

Thanks Erland for your reply, much appreciated. The row does contain a special character like the question mark inside a box in this following image

and also could potentially have other special characters like the Spanish tilde ~ on top of the n and some others in this following image

Including and factoring my example images above...if I understand correctly your first option of simply changing that Column datatype to varchar(max) would solve it and not cause any data loss (please correct me if I'm wrong). But if we went with your second option mentioned "But you may want to increase the max length or make it varchar(MAX) in that case." could it potentially have data loss in keeping it still as varchar ? Regards.

Erland Sommarskog 109.6K Reputation points MVP

2024-02-06T22:35:43.75+00:00

What is the current collation of the column?

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

techresearch7777777 1,841 Reputation points

2024-02-06T22:44:27.96+00:00

(Sorry if this is duplicate response as looks like nothing showed up when I initially followed up here and pushed the "Add comment" button.) It's currently set at: SQL_Latin1_General_CP850_CS_AS Thanks.

Erland Sommarskog 109.6K Reputation points MVP

2024-02-06T22:50:26.2666667+00:00

That is not a very good collation if you want to capture any character. ñ should work, since it's in code page 850. But for instance, you cannot store the character ć in that code page.

If you want full Unicode support, you would do one of

Change the data type to nvarchar.

Change the collation to Latin1_General_100_CS_AS_SC_UTF8.

If you want to change the max length to something > 1000 or go all the way to MAX while you are at it.

In no case there will be no data loss.

But both alternatives are size-of-data operations; that is the table has to be rewritten under the covers. So the operation can take some time.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Accommodate Column that contains special characters without existing data loss ?

0 additional answers

Your answer