SQL Database Collation

Question

Hello,

How we can change database collation without losings any data?

Answer 1

Generally, changing the collation is a difficult operation. Exactly how difficult depends on what collations you are changing between, and whether there are indexes or key constraints on your string columns.

Note that when you say

ALTER DATABASE db COLLATE NewCollation

This only affects the system tables, but user tables are not affected. To change the collation for user tables, you need to run

ALTER TABLE tbl ALTER COLUMN col COLLATE NewCollation

You need to repeat the data type, nullability etc in the ALTER COLUMN command. If there are indexes on a column, it needs to be dropped first. Same goes for PK/FK constraints.

To answer your question about losing data. The only time you can get data loss with a collation change is if the old and new collations have different code pages, and you have (var)char columns. n(var)char are not affected. You get data loss when the data has characters that are in the old code page, but are absent in the new code page. In this case, the missing characters will be replaced by fallback characters.