Sql Server排序规则的简介、选择、应用

用SQL语句查询当前数据库的默认排序规则

use db_name
exec sp_helpsort
go

 

--以下转自：http://hi.baidu.com/jztchina/blog/item/17b1e01f3812f169f724e479.html

Sql Server排序规则的简介、选择、应用

2009-11-07 21:11

一、排序规则简介： 什么叫排序规则呢？MS是这样描述的："在 Microsoft SQL Server 中， 字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存 储和比较字符所使用的规则。" 　　在查询分析器内执行下面语句，可以得到SQL　SERVER支持的所有排序规则。 　　　　select * from ::fn_helpcollations() 排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。 如： 　　Chinese_PRC_CS_AI_WS 前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则，按拼音排序。 Chinese_PRC_Stroke 表示按汉字笔画排序； 排序规则的后半部份即后缀 含义： 　　_BIN 二进制排序 　　_CI(CS) 是否区分大小写，CI不区分，CS区分(case-insensitive/case-sensitive) 　　_AI(AS) 是否区分重音，AI不区分，AS区分(accent-insensitive/accent-sensitive) 　　_KI(KS) 是否区分假名类型,KI不区分，KS区分(kanatype-insensitive/kanatype-sensitive) 　　_WI(WS) 是否区分宽度 WI不区分，WS区分(width-insensitive/width-sensitive)　 区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。 区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项， 比较还将重音不同的字母视为不等。 区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。 区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项。 二、排序规则选择： 如果 SQL Server 实例的所有用户都使用同一种语言，则应选取支持该语言的排序规则。例如，如果所有用户都讲法语，则选择法语排序规则。如果您的 SQL Server 实例的用户讲多种语言，则应选择能最好地满足各种语言需要的排序规则。例如，如果用户一般都讲西欧语言，则选择 Latin1_General 排序规则。 如果要支持讲多种语言的用户，则对于所有字符数据使用 Unicode 数据类型 nchar、nvarchar 和 nvarchar(max) 是非常重要的。Unicode 可避免非 Unicode 的 char、varchar 和 text 数据类型带来的代码页转换难题。因为排序规则定义用于比较操作的排序次序和 Unicode 字符的排序，所以当用 Unicode 数据类型实现所有列时，排序规则仍会产生不同。即使使用 Unicode 数据类型存储字符数据时，也应选择支持大多数用户的排序规则，以防使用非 Unicode 数据类型实现列或变量。 SQL Server 只支持由基础操作系统支持的代码页。在执行取决于排序规则的操作时，引用的对象所使用的 SQL Server 排序规则必须使用计算机上运行的操作系统所支持的代码页。 如果指定的排序规则（或引用的对象所使用的排序规则）使用 Windows 操作系统不支持的代码页，则 SQL Server 将发出错误。对此错误的响应取决于计算机上安装的 Windows 操作系统的版本。Windows 2000 及更新版本支持由 SQL Server 排序规则使用的所有代码页。因此，不会出现该错误消息。 三、排序规则的语法： Windows 排序规则名称由排序规则指示器和比较风格构成。 语法 < Windows_collation_name > :: =     CollationDesignator_<ComparisonStyle>     < ComparisonStyle > ::=         CaseSensitivity_AccentSensitivity         [_KanatypeSensitive [_WidthSensitive ] ]         | _BIN 参数 CollationDesignator 指定 Windows 排序规则使用的基本排序规则。基本排序规则包括： 当指定按字典排序时应用其排序规则的字母表或语言 用于存储非 Unicode 字符数据的代码页。 例如 Latin1_General 或法文，两者都使用代码页 1252，或土耳其文，它使用代码页 1254。 CaseSensitivity CI 指定不区分大小写，CS 指定区分大小写。 AccentSensitivity AI 指定不区分重音，AS 指定区分重音。 KanatypeSensitive Omitted 指定不区分大小写，KS 指定区分假名类型。 WidthSensitivity Omitted 指定不区分大小写，WS 指定区分大小写。 BIN 指定使用二进制排序次序。 注释 Microsoft® SQL Server™ 2000 Windows 排序规则的指示器为： SQL Server 2000 排序规则指示器 用于非 Unicode 数据的代码页 支持的 Windows 区域设置 Albanian 1250 阿尔巴尼亚语 Arabic 1256 阿拉伯语（阿尔及利亚）、阿拉伯语（巴林）、阿拉伯语（埃及）、阿拉伯语（伊朗）、阿拉伯语（约旦）、阿拉伯语（科威特）、阿拉伯语（黎巴嫩）、阿拉伯语（利比亚）、阿拉伯语（摩洛哥）、阿拉伯语（阿曼）、阿拉伯语（卡塔尔）、阿拉伯语（沙特阿拉伯）、阿拉伯语（叙利亚）、阿拉伯语（突尼斯）、阿拉伯语（阿拉伯联合酋长国）、阿拉伯语（也门）、波斯语、乌尔都语 Chinese_PRC 936 中文（香港特别行政区），中文（中华人民共和国），中文（新加坡） Chinese_PRC_Stroke 936 按汉字笔画排序（中华人民共和国） Chinese_Taiwan_Bopomofo 950 按汉语拼音排序（台湾） Chinese_Taiwan_Stroke 950 繁体中文（台湾） Croatian 1250 克罗地亚语 Cyrillic_General 1251 保加利亚语、白俄罗斯语、俄罗斯语、塞尔维亚语 Czech 1250 捷克语 Danish_Norwegian 1252 丹麦语、挪威语 (Bokmål)、挪威语（Nyorsk） Estonian 1257 爱沙尼亚语 Finnish_Swedish 1252 芬兰语、瑞典语 French 1252 法语（比利时）、法语（加拿大）、法语（卢森堡）、法语（标准）、法语（瑞士） Georgian_Modern_Sort 1252 按现代格鲁吉亚语排序 German_PhoneBook 1252 按德语电话号码簿排序 Greek 1253 希腊语 Hebrew 1255 希伯来语 Hindi 只用于 Unicode 数据类型 北印度语 Hungarian 1250 匈牙利语 Hungarian_Technical 1250   Icelandic 1252 冰岛语 Japanese 932 日语 Japanese_Unicode 932   Korean_Wansung 949 朝鲜语 Korean_Wansung_Unicode 949   Latin1_General 1252 南非荷兰语、巴斯克语、加泰罗尼亚语、荷兰语（比利时）、荷兰语（标准）、英语（澳大利亚）、英语（不列颠）、英语（加拿大）、英语（加勒比）、英语（爱尔兰）、英语（牙买加）、英语（新西兰）、英语（南非）、英语（美国）、法罗语、德语（奥地利）、德语（列支敦士登）、德语（卢森堡）、德语（标准）、德语（瑞士）、印度尼西亚语、意大利语、意大利语（瑞士）、葡萄牙语（巴西）、葡萄牙语（标准） Latvian 1257 拉脱维亚语 Lithuanian 1257 立陶宛语 Lithuanian_Classic 1257   Macedonian 1251 马其顿语 Mexican_Trad_Spanish 1252 西班牙语（墨西哥）、西班牙语（传统排序） Modern_Spanish 1252 西班牙语（阿根廷）、西班牙语（玻利维亚）、西班牙语（智利）、西班牙语（哥伦比亚）、西班牙语（哥斯达黎加）、西班牙语（多米尼加共和国）、西班牙语（厄瓜多尔）、西班牙语（危地马拉）、西班牙语（现代排序）、西班牙语（巴拿马）、西班牙语（巴拉圭）、西班牙语（秘鲁）、西班牙语（乌拉圭）、西班牙语（委内瑞拉） Polish 1250 波兰语 Romanian 1250 罗马尼亚语 Slovak 1250 斯洛伐克语 Slovenian 1250 斯洛文尼亚语 Thai 874 泰国语 Turkish 1254 土耳其语 Ukrainian 1251 乌克兰语 Vietnamese 1258 越南语 示例 下面是 Windows 排序规则名称的一些示例： Latin1_General_CI_AS 排序规则使用 Latin1 General 字典排序规则，代码页为 1252。不区分大小写但区分重音。 Estonian_CS_AS 排序规则使用爱沙尼亚字典排序规则，代码页为 1257。区分大小写并区分重音。 Latin1_General_BIN 排序规则使用代码页 1252 和二进制排序规则。忽略 Latin1 General 字典排序规则。 四、修改、查看排序规则： ------修改列的排序规则 ALTER TABLE tb ALTER COLUMN colname nvarchar(100) COLLATE Chinese_PRC_CI_AS ------修改数据库的排序规则 ALTER DATABASE database COLLATE Chinese_PRC_CS_AS ------查看某个表的排序规则 select collation from syscolumns where id=object_id(N'yourtablename') 五、排序规则应用： 　　SQL SERVER提供了大量的WINDOWS和SQLSERVER专用的排序规则，但它的应用往往 被开发人员所忽略。其实它在实践中大有用处。 　　例1:让表NAME列的内容按拼音排序： create table #t(id int,name varchar(20)) insert #t select 1,'中' union all select 2,'国' union all select 3,'人' union all select 4,'阿' select * from #t order by name collate Chinese_PRC_CS_AS_KS_WS drop table #t /*结果： id          name                 ----------- -------------------- 4           阿 2           国 3           人 1           中 */ 　　例2：让表NAME列的内容按姓氏笔划排序： create table #t(id int,name varchar(20)) insert #t select 1,'三' union all select 2,'乙' union all select 3,'二' union all select 4,'一' union all select 5,'十' select * from #t order by name collate Chinese_PRC_Stroke_CS_AS_KS_WS drop table #t /*结果： id          name                 ----------- -------------------- 4           一 2           乙 3           二 5           十 1           三 */ 排序规则应用扩展： 　　SQL SERVER汉字排序规则可以按拼音、笔划等排序，那么我们如何利用这种功能 来处理汉字的一些难题呢？我现在举个例子： 　　　　　　　　　　用排序规则的特性计算汉字笔划 　　要计算汉字笔划，我们得先做准备工作，我们知道，WINDOWS多国汉字，UNICODE目前 收录汉字共20902个。简体GBK码汉字UNICODE值从19968开始。 　　首先，我们先用SQLSERVER方法得到所有汉字，不用字典，我们简单利用SQL语句就 可以得到： select top 20902 code=identity(int,19968,1) into #t from syscolumns a,syscolumns b 再用以下语句，我们就得到所有汉字，它是按UNICODE值排序的： 　　select code,nchar(code) as CNWord from #t 　　然后，我们用SELECT语句，让它按笔划排序。 select code,nchar(code) as CNWord from #t order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code 结果： code        CNWord ----------- ------ 19968       一 20008       丨 20022       丶 20031       丿 20032       乀 20033       乁 20057       乙 20058       乚 20059       乛 20101       亅 19969       丁 .......... 　 从上面的结果，我们可以清楚的看到，一笔的汉字，code是从19968到20101，从小到大排，但到 了二笔汉字的第一个字“丁”，CODE为19969，就不按顺序而重新开始了。有了这结果，我们就可以轻 松的用SQL语句得到每种笔划汉字归类的第一个或最后一个汉字。 下面用语句得到最后一个汉字： create table #t1(id int identity,code int,cnword nvarchar(2)) insert #t1(code,cnword) select code,nchar(code) as CNWord from #t order by nchar(code) collate Chinese_PRC_Stroke_CS_AS_KS_WS,code select A.cnword from #t1 A left join #t1 B on A.id=B.id-1 and A.code<B.code where B.code is null order by A.id 得到36个汉字，每个汉字都是每种笔划数按Chinese_PRC_Stroke_CS_AS_KS_WS排序规则排序后的 最后一个汉字： 亅阝马风龙齐龟齿鸩龀龛龂龆龈龊龍龠龎龐龑龡龢龝齹龣龥齈龞麷鸞麣龖龗齾齉龘 　　上面可以看出：“亅”是所有一笔汉字排序后的最后一个字，“阝”是所有二笔汉字排序后的最后 一个字......等等。 　　但同时也发现，从第33个汉字“龗(33笔)”后面的笔划有些乱，不正确。但没关系，比“龗”笔划 多的只有四个汉字，我们手工加上：齾35笔，齉36笔，靐39笔，龘64笔 建汉字笔划表（TAB_HZBH）： create table tab_hzbh(id int identity,cnword nchar(1)) --先插入前33个汉字 insert tab_hzbh select top 33 A.cnword from #t1 A left join #t1 B on A.id=B.id-1 and A.code<B.code where B.code is null order by A.id --再加最后四个汉字 set identity_insert tab_hzbh on go insert tab_hzbh(id,cnword) 　　　　　select 35,N'齾' union all select 36,N'齉' union all select 39,N'靐' union all select 64,N'龘' go set identity_insert tab_hzbh off go 　　到此为止，我们可以得到结果了，比如我们想得到汉字“国”的笔划： declare @a nchar(1) set @a='国' select top 1 id from tab_hzbh where cnword>=@a collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id id          ----------- 8 (结果：汉字“国”笔划数为8) 　　上面所有准备过程，只是为了写下面这个函数，这个函数撇开上面建的所有临时表和固 定表，为了通用和代码转移方便，把表tab_hzbh的内容写在语句内，然后计算用户输入一串 汉字的总笔划： create function fun_getbh(@str nvarchar(4000)) returns int as begin declare @word nchar(1),@n int set @n=0 while len(@str)>0 begin set @word=left(@str,1) --如果非汉字，笔划当0计 set @n=@n+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 id from ( select 1 as id,N'亅' as word union all select 2,N'阝' union all select 3,N'马' union all select 4,N'风' union all select 5,N'龙' union all select 6,N'齐' union all select 7,N'龟' union all select 8,N'齿' union all select 9,N'鸩' union all select 10,N'龀' union all select 11,N'龛' union all select 12,N'龂' union all select 13,N'龆' union all select 14,N'龈' union all select 15,N'龊' union all select 16,N'龍' union all select 17,N'龠' union all select 18,N'龎' union all select 19,N'龐' union all select 20,N'龑' union all select 21,N'龡' union all select 22,N'龢' union all select 23,N'龝' union all select 24,N'齹' union all select 25,N'龣' union all select 26,N'龥' union all select 27,N'齈' union all select 28,N'龞' union all select 29,N'麷' union all select 30,N'鸞' union all select 31,N'麣' union all select 32,N'龖' union all select 33,N'龗' union all select 35,N'齾' union all select 36,N'齉' union all select 39,N'靐' union all select 64,N'龘' ) T where word>=@word collate Chinese_PRC_Stroke_CS_AS_KS_WS order by id ASC) else 0 end) set @str=right(@str,len(@str)-1) end return @n end --函数调用实例： select dbo.fun_getbh('中华人民共和国'),dbo.fun_getbh('中華人民共和國') 　 　　执行结果：笔划总数分别为39和46，简繁体都行。     当然，你也可以把上面“UNION　ALL”内的汉字和笔划改存在固定表内，在汉字 列建CLUSTERED INDEX，列排序规则设定为： 　　　 Chinese_PRC_Stroke_CS_AS_KS_WS 这样速度更快。如果你用的是BIG5码的操作系统，你得另外生成汉字，方法一样。 但有一点要记住：这些汉字是通过SQL语句SELECT出来的，不是手工输入的，更不 是查字典得来的，因为新华字典毕竟不同于UNICODE字符集，查字典的结果会不正 确。 　　           　　　　用排序规则的特性得到汉字拼音首字母 　　用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下： create function fun_getPY(@str nvarchar(4000)) returns nvarchar(4000) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0 begin set @word=left(@str,1) --如果非汉字字符，返回原字符 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then (select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC) else @word end) set @str=right(@str,len(@str)-1) end return @PY end --函数调用实例： select dbo.fun_getPY('中华人民共和国'),dbo.fun_getPY('中華人民共和國') 结果都为：ZHRMGHG 六、常见问题处理： 1.“无法解决 equal to 操作的排序规则冲突。” 示例1： create table #t1( name varchar(20) collate Albanian_CI_AI_WS, value int) create table #t2( name varchar(20) collate Chinese_PRC_CI_AI_WS,    value int ) 表建好后，执行连接查询： select * from #t1 A inner join #t2 B on A.name=B.name 这样，错误就出现了：            服务器: 消息 446，级别 16，状态 9，行 1            无法解决 equal to 操作的排序规则冲突。 　　要排除这个错误，最简单方法是，表连接时指定它的排序规则，这样错误就 不再出现了。语句这样写： select * from #t1 A inner join #t2 B on A.name=B.name collate Chinese_PRC_CI_AI_WS 示例2： 例如，在创建表时考虑使用下面的 Transact-SQL 语句： CREATE TABLE TestTab ( id int, GreekCol nvarchar(10) COLLATE greek_ci_as, LatinCol nvarchar(10) COLLATE latin1_general_cs_as ) INSERT TestTab VALUES (1, N'A', N'a') GO 该语句创建了一个包含以下两列的表：一列使用不区分大小写和区分重音的希腊语排序规则，而另一列使用区分大小写和重音的通用 Latin1 排序规则。 您可以尝试使用查询来显式比较这两列： SELECT * FROM TestTab WHERE GreekCol = LatinCol 但是，该查询会返回一个错误： Msg 446, Level 16, State 9, Server V-MICHKA3, Line 1 无法解决等于运算的排序规则冲突。 之所以会出现此错误，是因为服务器无法使用不同的排序规则来比较两段文本。但是，如果您使用 COLLATE 关键字显式创建一个允许这两列兼容的表达式，则查询将以如下方式执行： SELECT * FROM TestTab WHERE GreekCol = LatinCol COLLATE greek_ci_as 还需注意的是，尽管 LatinCol 通常有一个区分大小写的排序规则，但表达式不区分大小写的排序规则会将其覆盖，从而使“A”的大写和小写被视为等同。