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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:
原因:
在运行一个纬度抽取调度包时出现错误提示,通过定位发现 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 DATABASENAME..USERNAME.T_BASIC_TABLE 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 [database_XXX] ON
(NAME = N'BIDW_ICD_PRI_DAT', FILENAME = N'E:XXXDATAFILEdatabase_data.dat' , SIZE = 100, FILEGROWTH = 100)
LOG ON
(NAME = N'BIDW_ICD_LOG', FILENAME = N'E:XXXLOGFILEdatabase_log.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()
 
具体修改脚本如下:
CREATE TABLE [t_dem_XXX] (
[ID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name1] [varchar] (4) COLLATE Chinese_PRC_CI_AS NULL,
[Name2] [varchar] (5) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[Name3] [numeric](4, 0) NOT NULL ,
[Name4] [varchar] (20) COLLATE Chinese_PRC_CS_AS NOT NULL ,
[Name5] [varchar] (30) COLLATE Chinese_PRC_CS_AS NOT NULL
) GO
 





本文转自baoqiangwang51CTO博客,原文链接:http://blog.51cto.com/baoqiangwang/312797 ,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
2月前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
100 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
3月前
|
SQL 数据库 数据安全/隐私保护
SQL Server数据库Owner导致事务复制log reader job无法启动的解决办法
【8月更文挑战第14天】解决SQL Server事务复制Log Reader作业因数据库所有者问题无法启动的方法:首先验证数据库所有者是否有效并具足够权限;若非,使用`ALTER AUTHORIZATION`更改为有效登录名。其次,确认Log Reader使用的登录名拥有读取事务日志所需的角色权限。还需检查复制配置是否准确无误,并验证Log Reader代理的连接信息及参数。重启SQL Server Agent服务或手动启动Log Reader作业亦可能解决问题。最后,审查SQL Server错误日志及Windows事件查看器以获取更多线索。
|
11天前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。
|
2月前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
31 4
|
2月前
|
SQL 存储 数据管理
SQL Server数据库
SQL Server数据库
51 11
|
3月前
|
SQL Java 数据库
jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上
该博客文章介绍了在JSP应用中使用Servlet查询SQL Server数据库的表信息,并通过JavaBean封装图书信息,将查询结果展示在Web页面上的方法。
jsp中使用Servlet查询SQLSERVER数据库中的表的信息,并且打印在屏幕上
|
3月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
310 3
|
3月前
|
SQL 关系型数据库 MySQL
SQL数据库和 SQLserver数据库
【8月更文挑战第19天】SQL数据库和 SQLserver数据库
58 2
|
3月前
|
SQL 存储 安全
SQL Server数据库创建
【8月更文挑战第19天】SQL Server数据库创建
36 1
|
3月前
|
SQL 关系型数据库 数据库
数据库空间之谜:彻底解决RDS for SQL Server的空间难题
【8月更文挑战第16天】在管理阿里云RDS for SQL Server时,合理排查与解决空间问题是确保数据库性能稳定的关键。常见问题包括数据文件增长、日志文件膨胀及索引碎片累积。利用SQL Server的动态管理视图(DMV)可有效监测文件使用情况、日志空间及索引碎片化程度。例如,使用`sp_spaceused`检查文件使用量,`sys.dm_db_log_space_usage`监控日志空间,`sys.dm_db_index_physical_stats`识别索引碎片。同时,合理的备份策略和文件组设置也有助于优化空间使用,确保数据库高效运行。
74 2