locked
How to change collation for a whole database by creating a new database and moving objects RRS feed

  • Question

  • Hello,

    We have a SQL Database (2012) for Siebel. We want to change the collation from Latin1_General_CI_AS to Latin1_General_BIN. Oracle advises us to create a new database with the right collation and moving all database objects. Is there any Microsoft documentation how to do this?

    Thanks and regards,

    Ron Schoon

    Wednesday, March 7, 2018 7:26 AM

Answers

  • Just to clarify: After changing collation for the database (which only changes the default collation), you have to do ALTER TABLE ... ALTER COLUMN for every string column in the database. And you cannog have indexes, keys and such stuff when you do that ALTER. This is a serious undertaking, which is why we prefer to export into a new database in vast majority of cases.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, March 7, 2018 7:42 AM

All replies

  • You can directly alter the database collation in sql server , but some times you will get error stating that some dependent objects are there.. in that case you need to drop all those dependent constraints and indexes and change the collation in database level and then recreate the dependent indexes and constraints  

    sample query 

    ALTER DATABASE DB name COLLATE Latin1_General_BIN;

    Wednesday, March 7, 2018 7:35 AM
  • Just to clarify: After changing collation for the database (which only changes the default collation), you have to do ALTER TABLE ... ALTER COLUMN for every string column in the database. And you cannog have indexes, keys and such stuff when you do that ALTER. This is a serious undertaking, which is why we prefer to export into a new database in vast majority of cases.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, March 7, 2018 7:42 AM
  • That task is pretty complicated doing on the existing database. There is a third party tool called SQLCompare and SQL DataCompare from www.redgate.com. 

    Create an empty database with right collation 

    CREATE DATABASE dbname COLLATE <.....>

    Now that by using those tools move the structure  and the the data


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 7, 2018 7:52 AM