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,
而排序规则仍对大小写不敏感,在示例二及(续)中已证明。
***********************************/