COLLATE函数与字符集排序规则的思考

COLLATE函数与字符集排序规则的思考

 

/****

by select left('claro',2),in 西安,2009-03-24 14:21:34.250

Microsoft SQL Server 2005 - 9.00.1406.00

 (Intel X86)

   Mar  3 2007 18:40:02

   Copyright (c) 1988-2005 Microsoft Corporation

  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

****/

--COLLATE 子句只能应用于char、varchar、text、nchar、nvarchar 和ntext 数据类型。

--可应用于数据库定义或列定义以定义排序规则，或应用于字符串表达式以应用排序规则转换。

 

--语法

COLLATE { <collation_name> | database_default }

<collation_name> :: =

     { Windows_collation_name } | { SQL_collation_name }

 

--示例一：为查询字符集指定排序规则(对于数据库安装时已设置不同字符集规则的最有效方式)

--字符集大小写敏感

DECLARE @t table (col varchar(10))

insert @t

SELECT 'a' union all

select 'A' union all

SELECT 'a' union all

select 'A'

 

SELECT * FROM @t

 ORDER BY col COLLATE Chinese_PRC_CS_AS

-->结果

       a

       a

       A

       A

--字符集大小写不敏感

DECLARE @t table (col varchar(10))

insert @t

SELECT 'a' union all

select 'A' union all

SELECT 'a' union all

select 'A'

 

SELECT * FROM @t

 ORDER BY col COLLATE Chinese_PRC_CI_AS

-->结果

       a

       A

       a

       A

 

 

--示例二：虽可以修改数据库指定的排序规则(对于数据库安装时已设置字符集规则的无效)

--新建数据库并指定大小写不敏感

CREATE DATABASE db1 COLLATE Chinese_PRC_CI_AS

GO

--查询数据库字符集

SELECT dtb.collation_name AS [Collation],

dtb.name AS [DatabaseName2]

FROM

master.sys.databases AS dtb

WHERE

(dtb.name=N'db1')

-->结果

    Collation  DatabaseName2

    Chinese_PRC_CI_AS db1

 

--修改指定数据库排序规则（大小写敏感）

ALTER DATABASE [db1] COLLATE Chinese_PRC_CS_AS

GO

--查询数据库字符集

-->结果

    Collation  DatabaseName2

    Chinese_PRC_CS_AS db1

 

 

--示例二(续)：虽可以修改表中的列指定的排序规则(对于数据库安装时已设置字符集规则的无效)

use db1

 

CREATE table db2 (

col1 varchar(10) ,

col2 varchar(10) COLLATE Chinese_PRC_CI_AS)

GO

 

insert db2

SELECT 'a','A' union all

select 'A','a' union all

SELECT 'a','A' union all

select 'A','a'

       --(4 行受影响)

--查询表指定列字符集

select * from db2

       col1   col2

       a   A

       A   a

       a   A

       A   a

 

--修改表中指定列的排序规则

ALTER TABLE db2 ALTER COLUMN col1 varchar(10) COLLATE Chinese_PRC_BIN

ALTER TABLE db2 ALTER COLUMN col2 varchar(10) COLLATE Chinese_PRC_BIN

GO

       --命令已成功完成。

--查询表指定列字符集

select * from db2

       col1   col2

       a   A

       A   a

       a   A

       A   a

 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'db1'

 

DROP DATABASE [db1]

/****** 对象:  Database [db1]    脚本日期: 03/24/2009 14:35:15 ******/

/***********************************

以上示例表明：数据库及表的排序规则默认与安装数据库时设置一致，后期修改无效，故示例一的方式较合理。

SELECT dtb.collation_name AS [Collation],

dtb.name AS [DatabaseName2]

FROM

master.sys.databases AS dtb

WHERE

(dtb.name=N'MASTER')

 

Collation  DatabaseName2

Chinese_PRC_CI_AS master

--安装时默认数据库字符集排行规则为Chinese_PRC_CI_AS，虽新建数据库并指定字符集为Chinese_PRC_CS_AS，

而排序规则仍对大小写不敏感，在示例二及(续)中已证明。

***********************************/