MS SQL DB中的归类更改 [英] Collation change in MS SQL DB
问题描述
Hello All，
我必须将DB排序规则更改为 Latin1_General_CI_AS（来自Latin1_General_CI_AI）。我的数据库有700多个表，每个表有40多个列，具有不同的数据类型。我的数据库大小为150 GB。
我尝试使用数据库进行更改 - >属性，但我有很多错误如下。
消息5075，16级，状态2，行3
统计'customergroupBase .ndx_Sync'依赖于数据库排序规则。如果架构绑定对象依赖于它，则无法更改数据库排序规则。删除对数据库排序规则的依赖关系，然后重试该操作。
因此，我计划使用以下步骤更改表中列的排序规则。
1.删除所有索引
2.使用下面的alter命令更改排序规则
ALTER TABLE TableName ALTER COLUMN [Name] nvarchar（160）COLLATE Latin1_General_CI_AS NULL
3.重新创建所有索引。
我的问题是，b
$
1.这是整理更改，我需要更改表格中的所有字段（列）吗？或者只需要更改使用字符类型和文本类型的字段（列）？
2.更改排序规则后，当我在DB中创建新表或列时， DB会自动应用新的排序规则Latin1_General_CI_AS还是DB会使用旧排序规则 - Latin1_General_CI_AI？
仅限您如果有的话，需要更改类型为（n）（var）（char）（和text / ntext）的列的排序规则。实际上，您无法更改其他列的排序规则，因为排序规则不适用于它们。
一旦您的ALTER DATABASE COLLATE命令完成，此排序规则将成为新列的默认排序。
请记住，当您重新应用外键，索引等时，您可能会因规则更改而出错。
最好的可能是编写整个数据库的脚本，在脚本上运行查找/替换以更改所有内容的排序规则，创建新数据库并复制所有数据。
Hello All,
I have to change DB collation to Latin1_General_CI_AS (from Latin1_General_CI_AI). My DB having 700+ tables and each table is having 40+ columns with different data types. My DB size is 150 GB.
I have tried to change using Database--> properties, but i got lot of errors like below.
Msg 5075, Level 16, State 2, Line 3
The statistics 'customergroupBase.ndx_Sync' 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.
So, I have plan to change collation of columns in table with below steps.
1. Drop all indexes
2. Change collation using below alter command
ALTER TABLE TableName ALTER COLUMN [Name] nvarchar(160) COLLATE Latin1_General_CI_AS NULL
3. Recreate all indexes.
My Question is,
1. it is a collation change, do i need to change all fields(columns) in table? or only need to change the fields(columns) that uses character types and text types?
2. After changing collation, when I create new table or columns in DB, will DB automatically apply new collation Latin1_General_CI_AS or will DB use old collation - Latin1_General_CI_AI?
You only need to change the collation of columns that are of the type (n)(var)(char) (and text/ntext) if you have any. In fact, you cannot change collation of other columns, since collations do not apply to them.
Once your ALTER DATABASE COLLATE command hs gone through, this collation will be the default for new columns.
Bear in mind that when you reapply foreign keys, indexes etc, you may get errors because the rules change.
The best may be to script the entire database, run a find/replace on the script to change the collation of everything, create a new database and copy all data over.
这篇关于MS SQL DB中的归类更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
