修改数据库的排序规则

要查看可用的排序规则，可以使用以下查询：
select * from fn_helpcollations()
where name like 'SQL_%'

现在我的数据库Test原本的排序规则是SQL_Latin1_General_CP1_CI_AS，我现在想要修改为SQL_Latin1_General_CP1_CI_AI。

执行
ALTER  DATABASE TEST
COLLATE SQL_Latin1_General_CP1_CI_AI
报如下错误：
Warning: Changing default collation for database 'TEST', which is used in replication. All replication databases should have the same default collation.
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform. the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'SM98' cannot be set to SQL_Latin1_General_CP1_CI_AI.

这是因为我这个数据库作为replication的发布端，它提示replication中的数据库应该保持一样的排序规则。于是，我现在先去修改目的端的数据库的排序规则。
ALTER  DATABASE AdventureWorks1
COLLATE SQL_Latin1_General_CP1_CI_AI
但还是提示错误：
Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform. the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'AdventureWorks1' cannot be set to SQL_Latin1_General_CP1_CI_AI.

修改数据库为单用户模式：
ALTER DATABASE AdventureWorks1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
再执行：
ALTER  DATABASE AdventureWorks1
COLLATE SQL_Latin1_General_CP1_CI_AI
结果又报错：
Msg 5075, Level 16, State 1, Line 1
The column 'computedcolumn.col3' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1
The object 'ufnLeadingZeros' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1
The object 'CK_NewEgg_SOBillingInformation2010' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1
The object 'CK_NewEgg_SOBillingInformation2006' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1

其实有很多类似的错误，我这里只贴出了几个典型的。
在深入解析SQL SERVER 2008一书中有看到说，一般来说，如果数据库中有任何CHECK约束，都不能修改排序规则。上面最后两个错误在这里也验证了这一说。
然后其他的也是依赖于原来的排序规则，
这下没辙了，根据网上的说法可以使用以下方法解决。
I have found an approach similar to Colins to fastest and easiest.  I recently had to change the collation sequence on two diffrent data warehouse databases.  What I did was script. the entire database (users, objects, keys, everything) and then edit the script. in QA and change all the collation sections to be the collation I wanted.  (simple search and replace)  Then run the script. on a separate server.  That will create the database as a shell.  Then import data from the original database to the new using DTS.  Providing you created the database first, DTS will not bring in the original collation as it will use the existing one on the new database. Once you are done doing that you can detach the original and attach the new one.
这段话里有一点值的我们注意，其实我们在修改整个数据的排序规则后，只会影响数据库的元数据的排序规则，并不影响数据库中现存用户数据的排序规则，只是影响后续用户新添加或者修改的数据排序规则。

看样子，那现在我想要改变我test（同步练源端）数据库及AdventureWorks1（同步练目的端）的排序规则，那我只能先删掉同步练关系，因为我数据库（TEST）中没有使用到CKECK约束和其他依赖以前排序规则的列，所以应该可以直接修改成功。
先将数据库设为单用户模式，否则还是无法修改：
ALTER DATABASE TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE
然后再修改
ALTER  DATABASE TEST
COLLATE SQL_Latin1_General_CP1_CI_AI
最后记得把数据模式改为多用户模式：
ALTER DATABASE TEST SET MULTI_USER WITH ROLLBACK IMMEDIATE
现在来看我当前库的排序规则

AdventureWorks1库的修改就麻烦了，必须按照上面建议的方式来，先新建一个数据库，新建数据库的排序规则当然要指定为SQL_Latin1_General_CP1_CI_AI，然后将原来旧的数据库里的所有对象的创建脚本导出来，对于表上的一些collation用替换的方式都改为我们修改后的collation，然后再将这些脚本在新的数据库里run一下，最后将原数据库中表的数据都导入到新的数据库里，最后再通过重命名的方式，将新数据库命名为我们最终数据库名。这里我就不再做测试。

最后我还需要再测试一个问题，如果两个数据库的collation不同的话，那我们在这两个库之间建立事务复制会不会成功：
在创建的过程中有如下提示信息：
Msg 468, Level 16, State 9, Line 29
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
但是我的同步练确创建起了，而且可以正常同步。我猜想这是因为这两个排序规则至少是兼容的，所以可以创建成功，如果两个完全不兼容，那就应该无法创建成功。


总结一下：

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27026361/viewspace-774048/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27026361/viewspace-774048/