sql server中字符集和排序规则到底什么关系

jason0401

　　--SQLSERVER 中的排序规则 服务器》数据库》表列
　　--------------------------------- 排序规则简介 -------------------------------
　　-- 什么叫排序规则呢？ MS是这样描述的： " 在Microsoft SQL Server 2000 中，
　　-- 字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存
　　-- 储和比较字符所使用的规则。 "
　　-- 　　在查询分析器内执行下面语句，可以得到 SQL 　SERVER 支持的所有排序规则。
　　--
　　-- 　　　　select * from ::fn_helpcollations()
　　--
　　-- 排序规则名称由两部份构成，前半部份是指本排序规则所支持的字符集。
　　-- 如：
　　-- 　　Chinese_PRC_CS_AI_WS
　　-- 前半部份：指 UNICODE字符集， Chinese_PRC_ 指针对大陆简体字 UNICODE的排序规则。
　　-- 排序规则的后半部份即后缀含义：
　　-- 　　_BIN 二进制排序
　　-- 　　_CI(CS) 是否区分大小写， CI 不区分，CS 区分
　　-- 　　_AI(AS) 是否区分重音， AI 不区分，AS 区分　　　
　　-- 　　_KI(KS) 是否区分假名类型 ,KI 不区分，KS 区分　
　　--_WI(WS) 是否区分宽度 WI不区分， WS 区分　
　　--
　　-- 区分大小写 :如果想让比较将大写字母和小写字母视为不等，请选择该选项。
　　-- 区分重音: 如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，
　　-- 比较还将重音不同的字母视为不等。
　　-- 区分假名: 如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
　　-- 区分宽度: 如果想让比较将半角字符和全角字符视为不等，请选择该选项
　　-------------------------------------------------------------------------------------
　　sp_helpsort
　　SELECT SERVERPROPERTY ('Collation' )
　　--------------------------------------------
　　--2. 更改服务器排序规则
　　-- 更改SQL Server 2005 实例的默认排序规则的操作可能会比较复杂，包括以下步骤：
　　-- 确保具有重新创建用户数据库及这些数据库中的所有对象所需的全部信息或脚本。
　　-- 使用工具（例如大容量复制）导出所有数据。
　　-- 删除所有用户数据库。
　　-- 重新生成在 setup 命令的 SQLCOLLATION 属性中指定新的排序规则的 master 数据库。例如：
　　-- 复制代码
　　start /wait setup . exe / qb INSTANCENAME =MSSQLSERVER REINSTALL = SQL_Engine REBUILDDATABASE= 1 SAPWD= test SQLCOLLATION =SQL_Latin1_General_CP1_CI_AI
　　-- 有关重新生成 master 数据库的详细信息，请参阅如何重新生成 SQL Server 2005 的Master 数据库。
　　-- 创建所有数据库及这些数据库中的所有对象。
　　-- 导入所有数据。
　　-- 注意：
　　-- 可以为创建的每个新数据库指定默认排序规则，而不更改 SQL Server 2005 实例的默认排序规则。
　　-----------------------------------------------------------------------------------
　　--3. 设置和更改数据库排序规则
　　-- 创建新数据库时，可以使用下列内容之一指定排序规则：
　　--CREATE DATABASE 语句的COLLATE 子句。
　　--SQL Server Management Studio.
　　--SQL 管理对象(SMO) 中的 Database.Collation 属性。
　　-- 如果未指定排序规则，则使用服务器排序规则。

　　-- 可以使用ALTER DATABASE 语句的 COLLATE 子句来更改在用户数据库中创建的任何新对象的排序规则。使用此语句不能更改任何现有用户定义的表中列的排序规则。使用>　　-- 更改数据库排序规则时，需要更改下列内容：
　　-- 数据库的默认排序规则，这一新的默认排序规则将应用于数据库中后续创建的所有列、用户定义的数据类型、变量和参数。根据数据库中定义的对象解析 SQL 语句中指定的对象标识符时，也使用新的默认排序规则。
　　-- 将系统表中的任何 char、 varchar 、text 、 nchar、 nvarchar 或ntext 列更改为使用新的排序规则。
　　-- 将存储过程和用户定义函数的所有现有 char 、varchar 、 text、 nchar 、nvarchar 或 ntext 参数和标量返回值更改为使用新的排序规则。
　　-- 将char 、 varchar、 text 、nchar 、 nvarchar 或 ntext 系统数据类型和基于这些系统数据类型的所有用户定义的数据类型更改为使用新的默认排序规则。
　　--SQL code :
　　-------------------------- 数据库 ---------------------------------------
　　--1. 将数据库的字符集修改为：
　　ALTER DATABASE [pratice] COLLATE Chinese_PRC_CI_AS
　　--2. 为数据库指定排序规则
　　CREATE DATABASE db COLLATE Chinese_PRC_CI_AS
　　GO
　　ALTER DATABASE db COLLATE Chinese_PRC_BIN
　　GO
　　ALTER    DATABASE [pratice]  COLLATE   Chinese_PRC_CS_AS  -- 区分大小写
　　ALTER    DATABASE    [pratice] COLLATE  Chinese_PRC_CI_AS -- 不区分大小写
　　--------------------------- 表中的列 --------------------------------------
　　-- 为表中的列指定排序规则
　　CREATE TABLE tb
　　(
　　col1 VARCHAR (10 ) ,
　　col2 VARCHAR (10 ) COLLATE Chinese_PRC_CI_AS
　　)
　　GO
　　ALTER TABLE tb ADD col3 VARCHAR (10 ) COLLATE Chinese_PRC_BIN
　　GO

　　ALTER TABLE tb>　　GO

　　ALTER    TABLE    tb >
　　ALTER    TABLE    tb >　　---------------------------------------------------------------------
　　--3. 为字符变量和参数应用排序规则
　　DECLARE @a VARCHAR ( 10) ,
　　@b VARCHAR (10 )
　　SELECT   @a = 'a' ,
　　@b = 'A'
　　-- 使用排序规则 Chinese_PRC_CI_AS
　　SELECT   CASE WHEN @a COLLATE Chinese_PRC_CI_AS = @b THEN '@a=@b' --Chinese_PRC_CI_AS 不区分大小写
　　ELSE '@a@b'
　　END
　　-- 结果:@a=@b
　　-- 使用排序规则 Chinese_PRC_BIN
　　SELECT   CASE WHEN @a COLLATE Chinese_PRC_BIN = @b THEN '@a=@b'   --区分大小写
　　ELSE '@a@b'
　　END
　　-- 结果:@a@b
　　---------------------------SQL2000 适用 -----------------------------------------------------
　　-- 方法一安装 SQL2000时选择区分大小写或安装完以后重建 mastar ，选择区分大小
　　--C:\Program   Files\Microsoft   SQL   Server\80\Tools\Binn\rebuildm.exe
　　-- 方法二sql   server   8.0 以上的版本才可以，及其以下不支持
　　ALTER    DATABASE   [pratice]   COLLATE   Chinese_PRC_CS_AS
　　-- 修改排序规则，改成大小写敏感的排序规则

　　-- 如果只修改一个表，用>
　　-- 如果修改一个库的默认排序规则，用>　　-- 如果修改整个服务器的默认排序规则，用 Rebuildm.exe 重建master 库
　　-- 指定排序规则就可以了
　　-------------------------------------------------------------------
　　-- 注意需要使用 NVARCHAR()数据类型 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
　　-- 因为排序规则而出错的例子
　　--USE [tempdb]
　　--GO
　　--DROP TABLE #t1
　　--DROP TABLE #t2
　　CREATE TABLE #t1
　　(
　　value INT ,
　　name NVARCHAR ( 20) COLLATE Albanian_CI_AI_WS
　　)
　　CREATE TABLE #t2
　　(
　　value INT ,
　　name NVARCHAR ( 20) COLLATE Chinese_PRC_CI_AI_WS
　　)
　　INSERT   #t1
　　SELECT   1 ,
　　' 中'
　　UNION ALL
　　SELECT   2 ,
　　' 国'
　　UNION ALL
　　SELECT   3 ,
　　' 人'
　　UNION ALL
　　SELECT   4 ,
　　' 阿'
　　INSERT   #t2
　　SELECT   1 ,
　　' 亲'
　　UNION ALL
　　SELECT   2 ,
　　' 国'
　　UNION ALL
　　SELECT   3 ,
　　' 好'
　　UNION ALL
　　SELECT   4 ,
　　' 阿'
　　SELECT   * FROM    #t1 A INNER JOIN #t2 B ON A . name = B .name
　　-- 解决方法
　　SELECT   * FROM    #t1 A INNER JOIN #t2 B ON A . name = B .name COLLATE Chinese_PRC_CI_AI_WS
　　---------------------------------------------------------------------------------------
　　-- 例: 让表 NAME列的内容按拼音排序：
　　CREATE TABLE #t1
　　(
　　value INT ,
　　name NVARCHAR ( 20) COLLATE Albanian_CI_AI_WS
　　)
　　INSERT   #t1
　　SELECT   1 ,
　　' 中'
　　UNION ALL
　　SELECT   2 ,
　　' 国'
　　UNION ALL
　　SELECT   3 ,
　　' 人'
　　UNION ALL
　　SELECT   4 ,
　　' 阿'
　　SELECT   * FROM    #t1 ORDER BY name COLLATE Chinese_PRC_CS_AS_KS_WS
　　/* 结果：
　　id name
　　----------- --------------------
　　4 阿
　　2 国
　　3 人
　　1 中
　　*/
　　-------------------------------------------------------------------------
　　-- 例：让表NAME 列的内容按姓氏笔划排序：
　　create table #t (id int ,name NVARCHAR( 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 三
　　*/
　　--------------------------- 计算汉字的笔划 --------------------------------------------
　　-- 　　上面所有准备过程，只是为了写下面这个函数，这个函数撇开上面建的所有临时表和固
　　-- 定表，为了通用和代码转移方便，把表 tab_hzbh 的内容写在语句内，然后计算用户输入一串
　　-- 汉字的总笔划：
　　USE [tempdb]
　　GO
　　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 )
　　-- 如果非汉字，笔划当计
　　set @n= @n +(case when unicode (@word ) between 19968 and 19968 + 20901

　　then ( select top 1>
　　select 1 as>　　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>　　set @str=right( @str ,len ( @str)- 1 )
　　end
　　return @n
　　END
　　select dbo. fun_getbh (' 中华 '), dbo .fun_getbh ( '中華人民共和國 ' )
　　-- 简繁体都行
　　-------------------------------------------------------------------------
　　-- 用排序规则的特性得到汉字拼音首字母
　　-- 用得到笔划总数相同的方法，我们也可以写出求汉字拼音首字母的函数。如下：
　　USE [tempdb]
　　GO
　　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
　　-------------------------------------------------------------------------
　　-- 先用SQLSERVER 方法得到所有汉字，不用字典，我们简单利用 SQL 语句就可以得到：
　　select top 20902 code =identity ( int, 19968 ,1 ) into #t from syscolumns a, syscolumns b
　　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