store unicode characters in sql varchar 28 29 fields

1The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value.
2Sorry to say but the original value is lost.
3share  improve this answer  follow 
4answered Apr 27 '19 at 12:34
5
6SHORT ANSWER: The correct answer is do you have backups before this change?
7
8UNICODE IS A SPECIAL DATATYPE IN SQL SERVER
9
10As stated in one answer, UNICODE is a universal coding scheme designed to work with any other “coding page” Microsoft DOCS - UNICODE SUPPORT. Think of Unicode as a large character mapping scheme that contains many non-standard characters of foreign languages.
11
12SQL Server treats Unicode specially, with datatypes like NCHAR (fixed length), NVARCHAR (variable Unicode length) that will translate anywhere.
13
14Additionally, and very importantly, UNICODE uses two character lengths compared to regular non-Unicode Characters. This is because that “map” has to be big enough to work with the special sizes of Unicode characters.
15
16The storage size is two times n bytes + 2 bytes. nchar and NVARCHAR - Microsoft Docs This is twice the size of regular char/varchar and covers surrogate-pair key characters (not necessarily unique to Uni-code).
17
18PROBLEM: VARCHAR HAS NO MATCH TO UNICODE BUT INSERTED ANYWAYS
19
20Basically, the reason for “?” is because there is no matching equivalent for varchar was found. Unfortunately, SQL Server does not have a native way of preventing or warning you about these implicit conversions so the data is lost during insertion or modification.
21
22SOLUTION: RESTORE OR RECREATE
23
24Thus, you must restore or recreate the entries.
25
26Was this a recent change? Restore to the latest backup before the change. Was this a design that has been around for a while? Then, see if the rows can be recreated. Otherwise, there is no hope of recovering data that was lost.