SQL Database Collation

Handian Sudianto 4,431 Reputation points
2023-11-24T08:08:50.1533333+00:00

Hello,

How we can change database collation without losings any data?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,310 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2023-11-24T15:53:35.3933333+00:00

    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful