SQL Server Performance Forum – Threads Archive

ALTER DATABASE .. COLLATE

What does this do under the covers? I thought it was just changing the DEFAULT collation of a database. I’m running it on a dev server database and it’s taking AGES and creating a fairly large amount of t-log. It seems to be running CREATE INDEX. I’m hoping these are system table reindexes, cos I was under the impression that ALTER DATABASE. COLLATE. .. to change collation doesn’t affect existing data tables and their indexes? Can anyone help? BOL seems a bit vague. Tom Pullen
DBA, Oxfam GB

Any chance of running PROFILER during this operation, if planning to run again. You’re right the COLLATE clause will not have any processing on the existing data. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I don’t think profiler will be much help because all it will capture will be my own statement, not what it’s doing behind the scenes? Tom Pullen
DBA, Oxfam GB

Check out the weird stuff you get in the error log after changing collation.. 2005-01-31 10:46:37.67 spid54 Non-clustered index restored for userdb.sysobjects.
2005-01-31 10:46:37.70 spid54 Non-clustered index restored for userdb.sysobjects.
2005-01-31 10:46:37.79 spid54 index restored for userdb.syscolumns.
2005-01-31 10:46:37.85 spid54 index restored for userdb.systypes.
2005-01-31 10:46:37.85 spid54 index restored for userdb.sysusers.
2005-01-31 10:46:37.85 spid54 index restored for userdb.sysproperties.
2005-01-31 10:46:37.87 spid54 index restored for userdb.sysfulltextcatalogs.
2005-01-31 10:46:37.87 spid54 index restored for userdb.sysfilegroups.
2005-01-31 10:46:37.90 spid54 index restored for userdb.Ask_Type. What is it DOING? Tom Pullen
DBA, Oxfam GB

Looks like the ALTER DATABASE …. COLLATE command changes the collation on the system tables, possibly only on the indexes on system tables – or on both. In that case, if you have any objects that run queries joining a user table and a system table, you’ll have to check if they still run without an error for the collation.

there’s more to it than that; changing the default collation of the db is the easy bit! you have to change the collation of every single char or varchar column. these can only be changed if you have no objects dependent on those columns – these include indexes, primary keys, foreign keys, unique or other constraints, even statistics. these all have to be dropped first. it’s an inordinate amount of hassle. i’d grin and bear it if I’d made a mistake on the collation – but I didn’t .. it’s from a database upgraded from 6.5 where there’s no choice in the upgrade tool. grrrrrrrrrr. Tom Pullen
DBA, Oxfam GB

I think you’ll need to do a complete scripting of all objects in the database, then in that script remove any specific collation statements in the CREATE TABLE parts. Open the script in Word and do find-and-replace there: you may find just one version of the COLLATE clause throughout. (Just to be on the safe side, you could replace with COLLATE DATABASE_DEFAULT or COLLATE <your_collation> clauses where appropriate.) Next, create a new emtpy database on the target server using that edited script. Next, move all the data from the old database into the new one — and that’s a point where you need to pay attention, because I honestly don’t know what the different collations would do to the data. You may need to specify the COLLATE clause next to each char, varchar and text field in the SELECT part of your INSERT statements. So does that sound like a workable scenario?

Using ALTER TABLE .. ALTER COLUMN .. datatype COLLATE new_collation is enough, I don’t need to move the data or recreate all the objects. It’s just getting rid of all the dependent objects is a pain in the arse, auto-generating the scripts for DROP INDEx and aLTER TABLE DROP CONSTRAINT… major pain in the arse, all I can say is, thank god for the INFORMATION_SCHEMA views!!
Tom Pullen
DBA, Oxfam GB

]]>