SQL Collation Change

Question

Hello, My SQL collation is Latin1_General_CI_AS and i want to change to SQL_Latin1_General_CP1. Can we change the collation without losing any data?

Answer 1

Thanks for your question. Kindly check out the related thread below, as it contains a comprehensive discussion and a possible solution of your question. https://learn.microsoft.com/en-us/answers/questions/1131774/ms-sql-amending-collation?orderBy=Oldest Hope it will help! Thank you!

Answer 2

Changing a collation is a nightmare in the general case.

There are some good news in your case: Switching from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AS is one of the easier changes. Since the code page is the same there is no risk for data loss, or constraints blowing up. (Well, almost there are a few odd characters that are treated differently in SQL_Latin1_General_CP1_CI_AS, but let's assume you don't have them.)

Still it can be quite a pain. If you have CHECK constrains, you need to drop and recreate them.

Changing the database collation only changes the collation for metadata. User tables are unaffected. You need to run ALTER TABLE ALTER COLUMN on each column. To do that you need to drop indexes and foreign keys.

My friend and MVP colleague Hugo Kornelis has a blog post with a script that can help you: https://sqlserverfast.com/blog/hugo/2019/01/collation-change-script/ Towards the end of the comments there is a link to a Github site that has a later version.

Answer 3

Hi @Handian Sudianto

Refer to this doc: Set or change the database collation.

Also, it is recommended that you check the database and perform a backup before you change the collation at the database level.

Here is an article which describes a 7-step procedure how to change database collation.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.