什么是排序规则(collation)
关于SQL Server的排序规则,估计大家都不陌生,在创建数据库时我们经常要选择一种排序规则(conllation),一般我们会留意到每一种语言的排序规则都有许多种,比如标准大陆简体中文Chinese_PRC的排序规则就有数十种之多
这些排序规则有什么作用呢?让我们先来看看MS官方的解释: 排序规则指定了表示每个字符的位模式。它还指定了用于排序和比较字符的规则。排序规则具有下面的特征: 语言
区分大小写
区分重音
区分假名
比如在SQL Server 2005中,排序规则名称由两部份构成,比如 Chinese_PRC_CI_AI_WS
前半部份是指本排序规则所支持的字符集,如Chinese_PRC 指针对大陆简体字UNICODE的排序规则。
后半部份即后缀的含义如下: _BIN 指定使用向后兼容的二进制排序顺序。
_BIN2 指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。
_Stroke 按笔划排序
_CI(CS) 是否区分大小写,CI不区分,CS区分
_AI(AS) 是否区分重音,AI不区分,AS区分
_KI(KS) 是否区分假名类型,KI不区分,KS区分
_WI(WS) 是否区分全半角,WI不区分,WS区分
既然排序规则如此复杂,那么应用了不同排序规则的列之间默认情况下便不能进行Union、Join、Like等equal操作了,于是便有了排序规则(collation)冲突。
排序规则(collation)冲突
我们知道,SQL Server 从2000 开始,便支持多个排序规则。SQL Server 2000 的数据库可使用除默认排序规则以外的其他排序规则。此外,SQL Server 2000 还支持为列专门制定排序规则。
这样一来,我们在写跨表、跨数据库、跨服务器操作的T-SQL时,如果equal的字段排序规则不同,便会发生排序规则冲突。
比如我们先见两个结构相同的表,但字段的排序规则不同: -- 1. Create TableA.CREATE TABLETagsTableA(TagNameNVARCHAR(64)COLLATEChinese_PRC_BIN)-- 2. Create TableB.CREATE TABLETagsTableB(TagNameNVARCHAR(64)COLLATEChinese_PRC_CI_AS)
当表建好之后执行:
-- 3. Try to join themSELECT*fromTagsTableA AINNER JOINTagsTableB BonA.TagName=B.TagName
便会出下类似下面的问题:
无法解决 equal to 操作中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。
常见的场景——临时表
我们知道,SQL Server的临时表是保存在Tempdb数据库中的。而使用临时表的数据库与临时表的排序规则(conllation)不一定相同。所以,当Tempdb的排序规则与当前使用临时表的数据库排序规则不同时,便会出现排序规则冲突。
一般来说,我们在创建临时表时可能不会注意到排序规则,从而留下排序规则冲突的隐患。
比如Openlab V4.0的Blog模块中的一个存储过程,便有着这种隐患:
/****** 对?象ó: StoredProcedure [blogs].[up_CreateGetTagIds] 脚本日期: 01/20/2010 19:10:32 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO/*
RETURN VALUES:
Ids
*/
-- =============================================
-- Author:
-- Create date: <2010-01-06>
-- Description:
-- 1. Create Temp Table.
-- 2. Insert TagNames into Temp Table.
-- 3. Add new Tags to [Categories] from query Temp Table.
-- 4. Batch Get All Tag Ids from [Categories].
-- 5. Clear and drop Temp Table.
-- =============================================ALTER PROCEDURE[blogs].[up_CreateGetTagIds](@BlogIdINT,@TagNamesXML)AS
BEGIN/******************************* SET CONFIG *************************************************/SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NUMERIC_ROUNDABORT OFF/******************************* DECLARE VARIABLE *************************************************/
/********************************BEGIN TRANSATION**********************************************/BEGIN TRY
BEGIN TRANSACTION;-- 1. Create Temp Table.CREATE TABLE#TagsTable(TagNameNVARCHAR(64))-- 2. Insert TagNames into Temp Table.INSERT INTO#TagsTableSELECTTG.Tags.value('@i','NVARCHAR(64)')ASTagNameFROM@TagNames.nodes('/ts/t')TG(Tags)-- 3. Add new Tags to [Categories] from query Temp Table.BEGIN
INSERT INTO[Categories]([BlogId],[ParentId],[CategoryType],[CategoryName],[LoweredCategoryName],[Slug],[LoweredSlug],[Description],[CreatedDateUtc],[TotalEntities],[SortOrder],[State])SELECT@BlogId,0,-- ParentId, 0 as default.2,-- CategoryType, 2 as Post Tag.TT.TagName,LOWER(TT.TagName),TT.TagName,-- Slug, use CategoryName as default.LOWER(TT.TagName),-- LoweredSlug, use LoweredCategoryName as default.'',-- Description, Empty as default.GETUTCDATE(),0,-- TotalEntities, 0 as default.1,-- SortOrder of PostTags can always be 1.1-- State, 1 as Normal.FROM#TagsTable TTWHERELOWER(TT.TagName) NOT IN
(SELECTC.[LoweredCategoryName]FROM[Categories] CWITH(UPDLOCK,HOLDLOCK)WHERE[BlogId]=@BlogIdAND[CategoryType]=2-- Post Tag.)END-- 4. Batch Get All Tag Ids from [Categories].BEGIN
SELECT[CategoryId]FROM[Categories] CWITH(NOLOCK)
JOIN#TagsTable TTONC.[LoweredCategoryName]=LOWER(TT.TagName)WHEREC.[BlogId]=@BlogIdANDC.[CategoryType]=2-- Post Tag.ANDC.[State]=1-- 1 as Normal status.END-- 5. Clear and drop Temp Table.TRUNCATE TABLE#TagsTableDROP TABLE#TagsTableCOMMIT TRANSACTION;RETURN1END TRY
BEGIN CATCH
IFXACT_STATE() <>0BEGIN
ROLLBACK TRANSACTION;RETURN-1END
END CATCH
END
GO
常见的解决方案
知道了什么是排序规则冲突,我们接下来分析冲突的解决方案,以数据库级别的排序规则为例,一般来说,解决方案有下面几种
把SQL实例删了重建 ——大多数情况下等于没说-_-|||
修改数据库的排序规则 ——参考阿牛兄的这篇文章
在T-SQL中使用COLLATEDATABASE_DEFAULT来解决冲突 ——接下来主要讨论这个
COLLATE DATABASE_DEFAULT
CollateXXX操作可以用在字段定义或使用时,它会将字段定义或转换成XXX的排序规则格式。而CollateDatabase_Default则会将字段定义或转换成当前数据库的默认排序规则,从而解决冲突。
比如在下面的代码中便使用了CollateDatabase_Default来解决字段在equal操作中的排序规则冲突:
Insert into Security.Report(Name)SelectC.Path FromSSRS.CatalogCWhereC.Path CollateDatabase_DefaultLike@ReportPath+'/%'AndC.Path CollateDatabase_DefaultNot In (SelectNameFrom Security.Report R)
当然,在创建临时表时若对字段定义加上CollateDatabase_Default,也可以方便地解决潜在的排序规则冲突,比如上一节中提到的存储过程,只要做如下修改即可。
-- 1. Create Temp Table.CREATE TABLE#TagsTable(TagNameNVARCHAR(64)COLLATEDATABASE_DEFAULT)
结束语
对于专业的SQLer来说,排序规则的应用场景还有很多,例如利用排序规则特点计算汉字笔划和取得拼音首字母等等,更多信息,请查阅MSDN文档:http://msdn.microsoft.com/zh-cn/library/aa258237(en-us,SQL.80).aspx
谢谢!