关于解决SQLServer大小写敏感的办法

显示全部楼层 · 2015-3-4 14:19:44

原因：
在运行纬度抽取调度包时出现错误提示，通过定位发现p_dem_xxx存储过程运行失败。
分析：
维度抽取失败通常的情况下是维度表结构t_dem_xxx和业务系统基础表结构t_pub_xxx不一致，或者为字段长度不一致，或者类型不匹配，或者为主键不同，导致数据抽取时出现违反唯一性约束或字段被截取错误或者类型转换错误。
经过仔细检查发现这两个数据表结构都是一致的，并没有任何错误。然而再调试p_dem_Xxx仍发现报告说违反唯一性约束机制，检查左关联查询语句也没有发现问题。只好根据维度表结构t_dem_xxx提供的主键情况进行重复检查，却发现许多条重复，经过仔细检查重复记录发现重复记录的字段中是不区分大小写的。然后突然想到默认安装的SQLServer中是不区分大小写的。
解决步骤：
1.
备份各个数据库数据和脚本。
2.
通过查询SQLServer联机帮助文件，发现可以通过重建数据库改变实例的排序规则，于是未经思考，直接按照帮助中所提到的rebuildm方法(重建master，有兴趣可以查看联机帮助文件)对数据库进行重建，重建时选择中文，复选框选择大小写敏感，重建后发现所有的数据库均不见了，原来的工作白做了，只好重新建库。
3.
重新建库后，运行备份出的数据库脚本，却发现遇到了许多错误，运行不下去了，才想到，排序规则不仅只对字段文本的内容有效，同时也对所有数据库中所有对象(表、视图、字段、自定义函数、存储过程，系统函数不知道情况如何)均有效，没办法只好再重建数据库修改回来。
4.
通过重建方法需要修改东西太多了，有没有更简化的操作办法呢，再次查询联机帮助发现可以修改数据库的语言选项为COLLATE Chinese_PRC_CS_AS，表示为中文且大小写敏感，关于语言和排序规则的关系请执行select * from ::fn_helpcollations()，结果集中有详细说明，但问题是修改数据库需要修改大量的脚本，怪就怪在我们在写脚本是不注意编程规范，进行大批量的修改脚本也不太现实。
5.
既然修改实例和数据库排序规则都不可行，有没有办法进行最小的改动呢，只修改需要区分大小写的表或者字段，于是查看了一下数据库脚本导出方法导出的SQL脚本，发现字符性字段后面均带着Collate Chinese_RPT_CS_AS之类的东西，发现和之前字符集排序规则中的内容差不多，就想办法去修改，调试过程如下：
Create Table Test ( a varchar(20) Collate Chinese_RPT_CS_AS) on primary;
Insert into Test Values(‘a’)
Insert into Test Values(‘A’)
Select * from Test where a = ‘a’--结果为a A
也可以通过设计表的方式，点击字符型字段列属性的排序规则，可以对该列的排序规则进行设置，见下图

6.
心想问题总算解决了，又开始调试该存储过程，谁知道查询分析器又报告：
服务器: 消息 446，级别 16，状态 9，行 1
Cannot resolve collation conflict for equal to operation.
注：我们的脚本一般为
select a.a,a.b,a.c
from ORACLE..USERNAME.T_XXX a
left outer join t_dem_xxx b
on a.a=b.a and a.b=b.b
where ….
注：关于数据库链接和引用表一定要使用大写，我就犯了类似的错误。
7.
问题又冒出来了，只好又做测试，创建了一个相同字段的表(大小写不同)，经测试还是提示上面问题，没办法只好又建了一个一模一样的临时表，这些测试没问题了；才想到了即使是链接过来的表在SQLServer中默认也是不区分大小写的，尽管内容可以区分，于是想到把链接表内容直接写到新建的一模一样的临时表中，然后再用临时表和正式表进行关联，经测试OK。

附录：
SQLServer实例排序规则是无法提供脚本修改的，只能在初始安装时设定或者通过重建方式进行修改。
关于修改数据库的排序规则脚本
CREATE DATABASE [xxx]ON
(NAME = N'XXX_DAT', FILENAME = N'E:\xxx_data.dat' , SIZE = 100, FILEGROWTH = 100)
COLLATE Chinese_PRC_CS_AS--原来为Chinese_PRC_CI_AS
关于修改表中字段的排序规则脚本
Create Table Test
(
a varchar(20) Collate Chinese_RPT_CS_AS--原来为Chinese_PRC_CI_AS
) on primary;
关于查看系统字符集和排序规则的脚本
SELECT * FROM ::fn_helpcollations()

千问 · 2015-3-4 14:19:44

我以前也碰到过,也是这样解决的,不错,相信大家肯定会碰到此类问题

千问 · 2015-3-4 14:19:44

1.
Create Table Test ( a varchar(20) Collate Chinese_RPT_CS_AS) on primary;
Insert into Test Values(‘a’)
Insert into Test Values(‘A’)
Select * from Test where a = ‘a’ --结果为a A
--应该为Chinese_PRC_CI_AS
不建议改服务器或数据库为区分大小写,因为所有的系统表也会为大小写敏感了
另外, 链接服务器中有一个选项, 可以指定一个缺省的collation, 这样,Oracle表都用这个缺省的
collation
我没有试过这个功能,你可以试试, 把结果告诉我们

千问 · 2015-3-4 14:19:44

它不区分大小写的