Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Database collation Change Expand / Collapse
Posted Monday, April 28, 2014 4:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 27, 2016 7:51 AM
Points: 4, Visits: 50
Hi my Production DB's Server level Collation is SQL_Latin1_General_CP1_CI_AS
where as my development DB's Collation is Latin1_General_CI_AI ,often our development
team asks us to create a copy of the Production DB on our development Environment this becomes a big task for us because when we create a new DB and restore it with production copy the it will have a collation(SQL_Latin1_General_CP1_CI_AS) which is different from our Development server's collation

Can anyone please tell an easier way of changing the Collation of newly restored DB(without changing the Server level collation of our development DB)
Post #1565492
Posted Monday, April 28, 2014 4:18 AM



Group: General Forum Members
Last Login: Saturday, December 3, 2016 9:18 AM
Points: 2,983, Visits: 3,573
You can change the (default-)collation of a database with the ALTER DATABASE command. But this doesn't change the collation of the existing objects!! You need to change each existing object to the desired collation with an ALTER command. There's a script on codeplex to help you with this:

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1565498
Posted Monday, April 28, 2014 4:30 AM


Group: General Forum Members
Last Login: Thursday, March 17, 2016 5:13 AM
Points: 2,796, Visits: 2,232
Usually collation should be same on Prod and dev. Backup of the database also contain the details of collation and while restore it will keep the same collation. The only option is ALTER DATABASE command to change the collation.

"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1565506
Posted Monday, April 28, 2014 6:44 AM



Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 14,550, Visits: 38,420
changing the collation of the database is only a tiny 1% piece of the puzzle.

every varchar/char/nvarchar/nchar/text column in the database has it's own collation, that is not affected just because you change the database collation.

you have to change the individual columns as well, which can get very complex if defaults, check constraints, indexes or several other things are applied to any of those data type columns.

the default collation only affects newly created items that do not explicitly define their collation.

i made a script to change collation once, it worked for my situation, as i remember it it was a monster at 600 lines of code that took all those things into consideration.


help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1565553
Posted Wednesday, April 30, 2014 2:57 AM


Group: General Forum Members
Last Login: 2 days ago @ 7:17 AM
Points: 437, Visits: 1,670
Wouldn't creating a new DEV instance with server collation matching that of your production server be easier.
Post #1566271
Posted Wednesday, April 30, 2014 8:02 AM



Group: General Forum Members
Last Login: Tuesday, November 22, 2016 6:32 AM
Points: 5,519, Visits: 13,287
As others have pointed out, changing the collation for a database is not simple.
However, if you need to change it it's probably because some comparisons between columns in your database and columns in #temp tables are failing.

I see a couple of options here:

1) Add "COLLATE DATABASE_DEFAULT" to each CREATE #temp TABLE statement and/or directly in the comparison predicate. Not easy, but it will make your application more robust.
2) Change the dev server collation. Obviously, it will be feasible only if you don't have other databases in the dev instance that require a different collation
3) Build a script to change the collation of every column in the restored database, as Lowell suggested.

Lots of pain, no matter which one you choose.

Gianluca Sartori

How to post T-SQL questions
Post #1566410
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse