修改sql server数据库默认排序规则
修改sql server默认排序规则
在CMD模式：
1、 进入SQL Server 安装文件所在目录（setup.exe文件所在目录）
2、 运行命令：Net stop mssqlserver
3、 Setup /QUIET /ACTION=REBUILDDATABASE /instancename=mssqlserver /SQLSYSADMINACCOUNTS=administrator /sapwd=XXXXXX /sqlcollation=Chinese_PRC_CI_AS&
（sapwd是指sa 的密码）
命令格式是：Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName&
　　/SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ]&
　　/SQLCOLLATION=CollationName
4、 Net start mssqlserver
顶一下(0) 踩一下(0)
热门标签：Sql server中Collation conflict问题
Sql server中Collation conflict问题
SQL语句查询时
select A.Code,A.Name,a.Systemcode,B.ID,B.LogType,B.DMCode,B.IP,B.Department,B.CreateBy,B.CreateTime
from DM_Document_TD as A left join DM_LOG_TD as B
on A .Code=B .DMCode
结果在运行时碰到这样一个问题，问题描述：
Cannot resolve the collation conflict between &SQL_Latin1_General_CP1_CI_AS& and &Chinese_PRC_CI_AS& in the equal to operation.
到网上搜了一下，了解了问题产生的原因，原来是两个的collation不一致导致的，将查询语句改成如下：
select A.Code,A.Name,a.Systemcode,B.ID,B.LogType,B.DMCode,B.IP,B.Department,B.CreateBy,B.CreateTime
from DM_Document_TD as A left join DM_LOG_TD as B
on A .Code collate chinese_prc_ci_as =B .DMCode
(window.slotbydup=window.slotbydup || []).push({
id: '2467140',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467141',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467143',
container: s,
size: '1000,90',
display: 'inlay-fix'
(window.slotbydup=window.slotbydup || []).push({
id: '2467148',
container: s,
size: '1000,90',
display: 'inlay-fix'一、写在前面
　　最近公司进行开发环境升级，数据库也准备了一个新的服务器，一切准备好后开始数据迁移，采取的方式为对现有Database（现有服务器Windows Server 2003 + SQL Server 2005）进行Back up，然后在新服务器（Windows Server 2008 R2 + SQL Server 2008 R2）上再Restore Databse，一切似乎挺顺利的。但是当从Web Server开始访问的时候出现错误Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
二、问题分析
　　上面出现的问题是由于排序规则冲突（collation）而引发，具体说来就是数据库所使用的语言的排序规则冲突，查看数据库所（老服务器）使用的排序规则如下所示
　　而新服务器上数据库的排序规则为SQL_Latin1_General_CP1_CI_AS（就不上图了），由此可以看出排序规则不同，此即问题所在。
　　那么为什么数据库的排序规则会不同呢？换句话说数据库的排序规则是怎么来的？原来这里的排序规则是在安装SQL Server数据库实例时配置/指定的，默认的排序规则将基于操作系统的区域语言设置，我们新服务器OS的区域语言为US - English，而在安装SQL Server数据库实例时我们又没有配置排序规则（采取了默认的方式），所以才导致最终数据库的排序规则为SQL_Latin1_General_CP1_CI_AS
三、解决问题
　　下面列出解决此问题可能的方式以及说明
1. 在SQL语句中强行指定排序规则，类似的SQL语句如下所示 (COLLATE Chinese_PRC_CI_AI_WS)
SELECT A.ID, B.NO
FROM TABLEA A INNER JOIN TABLEB B
ON A.NAME=B.NAME COLLATE Chinese_PRC_CI_AI_WS
这种方式虽能解决问题，但是不建议采取，因为一旦需要更改的SQL语句很多，对开发者而言将是一种灾难，而且不能从根本上解决问题
2. 更改字段、表、数据库的排序规则
脚本更改数据库的排序规则
ALTER DATABASE DBNAME COLLATE Chinese_PRC_CI_AS
虽然通过更改数据库级别的排序规则能很大程度上解决大部分问题，但是一旦用到TempDB（确切地说使用到临时表，例如Store Procedure中），排序规则问题将再次面临，而且致命的是系统数据库master貌似无法更改排序规则，至少笔者通过脚本运行的时候提示没有权限运行，所以此种方式也没有从根本上解决问题。
说明：重建数据库也是不行的，只要master的排序规则没变你就无法解决问题。
3. 承接上面的第二点，既然无法通过脚本更改master数据库的排序规则，那么就重建master数据库
执行类似下面的脚本
setup.exe /q /ACTION=RebuildDatabase /INSTANCENAME=MSSQLSERVER /SAPWD="sa-pwd" /SQLSYSADMINACCOUNTS="BUILTIN\ADMINISTRATORS" /SqlCollation=Chinese_PRC_CI_AS
或参考MSDN的文章
笔者没有验证此种方式是否有效（笔者采用上面的脚本运行，但是没成功）
4. 卸载/重装SQL Server实例
首先更改操作系统的区域语言为CN - Chinese，然后卸载并重装SQL Server，之所以更改系统的区域语言设置，是为了在安装SQL Server的时候可以采用默认的排序规则（当然你也可以不更改区域语言设置，但在安装SQL Server的时候手工配置/指定排序规则为Chinese_PRC_CI_AS，到&排序规则设置&界面时，系统默认选择的是以下拉框的形式选择排序规则的选项），此种方法彻底解决问题，推荐使用。
四、写在最后
　　以后再做数据库迁移的时候，首先应该增加检查现有数据库的排序规则，然后在新服务器上安装数据库时，应确保和原有数据库的排序规则一致。引申一下：其实不仅排序规则如此，其它有可能引起冲突或不兼容的都应该相应地做个检查，做到升级或迁移之前解决问题，而不是把问题留到新服务器上，因为一旦时间紧迫，将是个灾难，尤其是正式库的升级。
阅读(...) 评论()Question：
What is Collation? How it will affect SQL Server database, and server?
字符的存储
&&&&&&& 在将collation之前，我们首先需要知道字符是如何被存储的。在计算机中，所有数据都是用0和1这样的位来描述。一个字节有8位，因此一个字节最多可以描述256个字符。在欧美国家，比如美国，他们的文字字符主要就是26个字母加上一些特殊符号(+-*/等)，用一个字节就可以存储，一个国家使用的所有字符就是一个code page，用一个字节存储字符的code page 叫做single-byte code page。但是在亚洲的一些国家，比如中国，常用汉字有几万个，根本不能用一个字节来表示所有的汉字字符，因此需要用两个字节描述。因为两个字节有16位，最多可以描述65536个字符，足够用来描述所有汉语字符以及常用字符，这些字符也是一个code page，不过是double-byte code page，主要针对的是中国。每个国家都有一个code page来对应所使用的字符。比如欧美国家，他们使用拉丁，虽然a-z这26个字母所对应的二进制在code page中是相同的，但是在重音（'&'和'&'）方面是不相同的。所以如果code page不同，那么相同的二进制代码所表示出来的字符也可能不相同。如果数据在不同code page的计算机上传输，就需要进行code page的转换，如果接收方的code page上没有定义传输方传送过来的特定字符的二进制位，那么就会出现数据丢失。
Collation的组成
&&&&&&&& 在讲完字符的存储形式以后，我们就可以讲什么是collation了。Collation描述了数据在数据库中是按照什么规则来描述字符，以及字符时如何被排序和比较的。在SQL Server中，Collation由两部分组成，比如中国的一个collation是 Chinese_PRC_CI_AI_WS ，前半部份是指的是所支持的字符集，与code page相对应，如Chinese_PRC 对应的代码页是936，在这个code page中定义了所有能够使用的字符。后半部CI_AI_WS用于表示排序规则，比如：
_CI(CS)表示是否区分字母大小写，CI不区分，CS区分。如果区分大小写，那么排序的时候小写字母的排在大写的前面；如果不区分大小写，那么排序的时候视大小写字母相同。
_AI(AS) 表示是否区分重音，AI不区分，AS区分。如果不区分重音，那么排序的时候视&a&和&?&为相同字符
_KI(KS) 表示是否区分假名类型，KI不区分，KS区分。在日语中应用。
_WI(WS) 表示是否区分全半角，WI不区分，WS区分。半角是单字节，全角是双字节。
Collation的四个级别
&&&&&& Collation一共有四个级别，分别是server-level, database-level, column-level和expression-level。
Server-level Collations：
&&&&&&& 服务器级别的collation是在安装数据库实例的时候指定的，如果没有特别指定，那么就将windows collation作为server-level collation。Windows collation由操作系统中的区域语言来决定的，如下图所示。
&&&&&&& 因为我们选择的是Chinese(Simplified,PRC)，那么我们默认的server-level collations就是：Chinese_PRC_。Server-level collation也是系统数据库和用户数据库的默认collation。一般情况下server-level collation一旦设定就不能更改，除非将所有数据库中的对象以及数据全部导出，并创建master，再将数据导回才可完成。
Database-level collations
&&&&&&& Database-level collations可以在create database&collate的时候指定，如果要修改database-level collations，可以通过alter database &collate来修改。一般情况是不能修改系统数据库（master等）的collations的，除非使用前面提到的修改server-level collations的方法来修改系统数据库。
Column-level collations
&&&&&&& 在创建或更改表时，可使用 COLLATE 子句指定每个字符串列的排序规则。当然也可以修改column-level collations。
Expression-level collations
&&&&&&& Expression-level是指在执行sql语句的时候指定collations，比如：
SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;
&这一条查询语句表示按照Latin1_General_CS_AI的排序规则来进行排序。Expression-level collations的一个好处就是非常灵活。
实验1：解决collation不匹配导致的冲突
&&&&&&& 在对两个collations级别不同的数据库的表进行连接操作的时候，会报错。这是可以通过expression-level collations来指定使用何种collations来解决问题。比如使用Collate Database_Default 则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。
创建两张表，第一张表使用默认的collation，第二张表在stuname列上指定collation。
create table student1
stuid int not null,
stuname nvarchar(20) not null,
create table student2
stuid int not null,
stuname nvarchar(20) COLLATE Latin1_General_CS_AI
--求表连接Step2：
select s1.*,s2.*
from student1 s1,student2 s2 where s1.stuname=s2.stuname
执行上述查询报错如下所示：
Cannot resolve the collation conflict between "Latin1_General_CS_AI" and "Chinese_PRC_CI_AS" in the equal to operation.
然后在expression-level使用Collate Database_Default
select s1.*,s2.*
from student1 s1,student2 s2 where s1.stuname=s2.stuname Collate Database_Default
上述查询执行成功。
需要注意的是collation只能用在字符串类型的列上面，如果在int列上使用collate会报错。
实验2：变更collation对数据库的影响
创建数据库，查看数据的默认database collation与server collation是否一样。
在database collation为Chinese_PRC_CI_AS的数据库中插入中文，然后修改collation为Latin1_General_CS_AI，看看已保存的数据有没有发生变化。如果再次把collation改回到Chinese_PRC_CI_AS，又有什么变化
在collation为Latin1_General_CS_AI的情况下，插入中文，会有什么情况，如何解决。
--实验1：测试nvarchar和varchar的存储长度
--创建一个默认collation为Chinese_PRC_CI_AS的数据库TESTDB3
USE TESTDB1
CREATE TABLE test
NVARCHAR(8) NOT NULL,--nvarchar类型，双字节存储
VARCHAR(8) NOT NULL, --varchar类型，单字节存储
insert into test values('姓名1','标题1');
select * from
insert into test values('','1');--String or binary data would be truncated.
insert into test values('','1');
insert into test values('1','');
insert into test values('一二三四五六七八','一二三四');
select * from
1.nvarchar(n)，按字符来存储，不论是英文字符还是中文字符。最多能够存储n个中文或者是英文，但是所占用的存储空间是2n+2个字节。1&=n&=4000
2.varchar(n)按字节存储，最多能够存储n个英文字母，存储n/2个中文字符。但是所占用的存储空间是n个字节。1&=n&=8000
--实验2：collation的变更对数据的影响。
USE TESTDB1
select * from
--step1：修改数据库的collation从默认的Chinese_PRC_CI_AS修改为Latin1_General_CS_AI，英语国家都是使用这个排序规则。
use master
collate Latin1_General_CS_AI
--step2：通过下面的语句可以查出实例中与默认collation不同的数据库，查找到了我们之前的 TESTDB1
use master
NAME AS DATABASE_NAME
, DATABASEPROPERTYEX(NAME,'COLLATION') AS DBCOLLATION
, SERVERPROPERTY('COLLATION') AS SERVERCOLLATION
FROM SYS.DATABASES
WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME,'COLLATION')) && SERVERPROPERTY('COLLATION')
--step3：在修改完collation以后查看表中的数据，发现数据没有改变。
USE TESTDB1
select * from
--step4：在新的collation下面插入数据
insert into test values('姓名1','标题1');--插入以后发现中文都变成了乱码"??"
select * from
--step5：因为lastname是nvarchar类型，我们在插入的时候指定出nvarchar
insert into test values(N'姓名2','标题2');--此时发现姓名没有乱码，标题不用说还是乱码??
select * from
--step6：试一试在varchar类型的字段插入nvarchar是数据
insert into test values(N'姓名3',N'标题3');--发现标题也不是乱码了。
select * from
------------------------------------
--step7：将collation从Latin1_General_CS_AI改回默认的Chinese_PRC_CI_AS
use master
collate Chinese_PRC_CI_AS
--step8：在修改完collation以后查看表中的数据，发现数据没有改变。
USE TESTDB1
select * from
--step9:插入字符，不指定unicode类型，查询显示的是中文，表明当前collation默认使用unicode编码。
insert into test values('姓名4','标题4');
select * from
collation的变更不改变数据库原先存储的数据，原来是怎么样，修改以后还是怎样，没有发生改变。
Latin1_General_CS_AI默认是的non-unicode的，所以在这个collation下插入中文变成乱码，必须在插入数据的时候指明使用unicode形式插入，也就是添加关键字&N&，而Chinese_PRC_CI_AS这个collation使用的是double-byte code page，这里面定义了所有中文字符，所以在插入数据的时候不需要指定关键字&N&。
可以往varchar数据类型的列中插入nvarchar的数据，也就是使用varcha存储unicode的数据。
阅读(...) 评论()SQL Server Collation Name (Transact-SQL)
SQL Server Collation Name (Transact-SQL)
Expand the table of content
SQL Server Collation Name (Transact-SQL)
THIS TOPIC APPLIES TO: SQL Server (starting with 2008)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse Is a single string that specifies the collation name for a SQL Server collation.SQL Server supports Windows collations. SQL Server also supports a limited number (&80) of collations called SQL Server collations which were developed before SQL Server supported Windows collations. SQL Server collations are still supported for backward compatibility, but should not be used for new development work. For more information about Windows collations, see .
&SQL_collation_name& :: =
SQL_SortRules[_Pref]_CPCodepage_&ComparisonStyle&
&ComparisonStyle& ::=
_CaseSensitivity_AccentSensitivity
A string identifying the alphabet or language whose sorting rules are applied when dictionary sorting is specified. Examples are Latin1_General or Polish.Pref
Specifies uppercase preference. Even if comparison is case-insensitive, the uppercase version of a letter sorts before the lowercase version, when there is no other distinction.Codepage
Specifies a one- to four-digit number that identifies the code page used by the collation. CP1 specifies code page 1252, for all other code pages the complete code page number is specified. For example, CP1251 specifies code page 1251 and CP850 specifies code page 850.CaseSensitivityCI specifies case-insensitive, CS specifies case-sensitive.AccentSensitivityAI specifies accent-insensitive, AS specifies accent-sensitive.BIN
Specifies the binary sort order to be used.To list the SQL Server collations supported by your server, execute the following query.
SELECT * FROM sys.fn_helpcollations()
WHERE name LIKE 'SQL%';
1For Sort Order ID 80, use any of the Window collations with the code page of 1250, and binary order. For example: Albanian_BIN, Croatian_BIN, Czech_BIN, Romanian_BIN, Slovak_BIN, Slovenian_BIN.
IN THIS ARTICLE
Was this page helpful?
Additional feedback?
1500 characters remaining
Thank you!
We appreciate your feedback.
Dev centers
Learning resources