Alter database collate throw an error on indexes

Question

Hi,

I tried to change the database collate, from latin1_General_CS_AS To CI_AS. It throws an exception (1505, a duplicate key was found) on Create Unique Index on object dbo.sysschobjs, witht the index name nc1.

I searched for the index nc1, and I found 24 indexes with the same name. One of them is defined into the sys.sysschobjs table, a System_table, that I cannot read. Maybe the duplicate key was on that table? How can I change the collate of my database?

Thank you
Lucio

Answer 1

Hi there, the problem is that because of the change in Collation from Latin_General_CS_AS to CI_AS your sensitivity to character case (CAPITAL or small cap) is removed and your may find yourself with identical values while removing your case sensitivity. You need to "rework" your data and make sure that no duplicates are found in the columns which are indexed (even the primary keys) after you convert everything to lower or to upper. when you convert collate to CI_AS make sure that there is no duplication in the name of database objects either for example you don't have two column or table which reads the same after conversion.

Answer 2

Changing the collation of a database is not really a walk in the park. In fact, it can be quite a bit of a nightmare.

As Hedaayt says. in this particular case, the issue is that you have two objects where the name differs only in case, for instance MyTable and mytable.

You can use this query to find the duplicates:

SELECT name COLLATE Latin1_General_CI_AS, COUNT(*)
FROM  sys.objects
GROUP BY name COLLATE Latin1_General_CI_AS
HAVING COUNT (*) > 1

Keep in mind that ALTER DATABASE only changes the collation of the system tables, and do not touch the user tables. These you need alter on your own. And if you have unique indexes on string columns, it is not going to be fun, but you are likely to get lot of collisions like these.