SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database collation Change


Database collation Change

Author
Message
bspavan86
bspavan86
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 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)
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8670 Visits: 3718
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:
http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7529 Visits: 2250
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."
Lowell
Lowell
SSC Guru
SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)SSC Guru (72K reputation)

Group: General Forum Members
Points: 72360 Visits: 40942
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.

Lowell
--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!
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1264 Visits: 1793
Wouldn't creating a new DEV instance with server collation matching that of your production server be easier.
Gianluca Sartori
Gianluca Sartori
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24157 Visits: 13362
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
spaghettidba.com
@spaghettidba
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search