Issues with converting existing data Column Collation Using ALTER TABLE and Copy-Replace Methods in SQL Server

Question

Hi All,

I'm encountering issues while attempting to change the collation of a column in SQL Server using the methods described in Microsoft's documentation but it is changing the existing data.

Specifically, I've tried both the in-place conversion and the copy-replace method in https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-ver16&tabs=ssms, but neither is working as expected.

Scenario:

SQL Server version : Azure SQL (standard) And SQL Server 2019

Column : MyString (VARCHAR(50))

Current collation : SQL_Latin1_General_CP1_CI_AS

Target collation : Chinese_Simplified_Stroke_Order_100_CI_AI

Methods Tried:

In-Place Conversion:

ALTER TABLE dbo.MyTable

ALTER COLUMN MyString VARCHAR(50) COLLATE Chinese_Simplified_Stroke_Order_100_CI_AI;

Issue: Doesn't changing the existing data to Chinese_Simplified_Stroke_Order_100_CI_AI.only the new data inserted by the insert statement got reflected in the table.

Copy and Replace Method:

CREATE TABLE dbo.MyTable2 (MyString VARCHAR(50) COLLATE Chinese_Simplified_Stroke_Order_100_CI_AI);

INSERT INTO dbo.MyTable2 ;

SELECT * FROM dbo.MyTable;

DROP TABLE dbo.MyTable;

EXEC sp_rename 'dbo.MyTable2', 'dbo.MyTable';

Issue: Collation is not applied for the data inserted into the new table from old table. The migrated records are shown as question mark ( ??? ). But when we insert a new row, that new data/record has the new language as per the collation.

Additional Context:

• The table has 5 rows with sample Chinese words in the rows

The table has no constraints such as Foreign Keys, Primary Keys, and Triggers.

Questions:

our Requirement is migrate or convert the existing underlying Chinese data (currently shown as ??? ) into readable format as per the collation

Are there any known issues or limitations with these methods ?

Is there a recommended alternative approach to change the collation apart from this two method without causing significant downtime or data inconsistency?

I appreciate any guidance or suggestions from the community. Thank you!

Best regards,

Mathan Kumar

Answer 1

Maybe the data are not available and were already replaced with '?' during insertion to original MyTable in the past. (The table really contain “?”; the text were lost). You should reinsert the corrected data to new table.