Erick Sasse wrote:
If I want to change the collate for all my varchar fields, is it safe to just do a bulk update on system tables? I want to avoid creating a new db and pumping data.
If I do the bulk update, backup, restore and don't get any errors, I should be ok, right?
Sean Leyne answers:
Nope.
This will not have changed how the data is stored in the db, only how it is defined.
Alexandre Benson Smith answers:
When I need to do this.. I did the following:
- extract metadata
- change all domains to use collate XYZ
- create an empty database with the modified collations
- pump the data over.
Of course it's not the quickest way of doing, but I am sure it's the safest. It's easier than it looks at first sight.
I would not use a quick and dirty method if I have one that is safe. The problems you will avoid is far greater than the time you will save.
If you have defined domains you only need to change in a dozen of places and the pump process is not that slow. And you always could read your favorite blog while the data is pumping around...
Now the problem I had encountered in my saga...
You will have some invalid data (violation of unique constraints) with the new accent/case insensitive collation.
Let's suppose you have an Unique Constraint on People.Name, then you have "José", "JOSÉ", "Jose" and "JOSE", all are unique in a case/accent sensitive collation, once you change it to PT_BR you will get Unique Constraints violations, so the data pump process will be the easiest problem you will have to solve
Erick Sasse continues:
So when I restore the db data is not restored to the proper format?
Ann W. Harrison answers:
Err, the collation doesn't affect the stored format of data. It does affect the stored format of indexes. The restore will create new indexes using the new collation (at least it should). However, as Alexandre said, using a data pump will make it much easier to correct problems that arise.