sqlserver之排序规则和ETL不支持sqlserverdatetime2的问题

 

sqlserver的排序规则大概分为Windows 排序规则和 SQL Server 排序规则。数据在安装的时候，默认不设置会默认为SQL_Latin1_General_CP1_CI_AI。数据库在创建的时候，如果不设定会使用数据默认的排序规则，此外，还可以对表中的列设定排序规则。

这里只是记录一下最近遇到这类问题时的一些注意事项。

 　　首先SQL_Latin1_General_CP1_CI_AI对应的1252，而Chinese_PRC_CI_AS对应gbk的936。如果要正确的保存中文，则需要在建库的时候，或者建表的时候，对库或者列时，设定个排序规则。一般默认应该是列服从数据库，数据库服从安装时的默认值。

接下来是本人遇到了问题以及解决的过程，记录一下，加深印象：

　　本人碰到的问题是使用ETL把数据从oracle抽取到sqlserver中，oracle的编码方式是UTF8。sqlserver的默认排序规则是SQL_Latin1_General_CP1_CI_AI。创建库的时候没有注意设置这个排序规则，使用了默认，导致sqlserver在无法识别时，使用？？来代替了原来的中文内容。后来发现了问题，由于修改默认的排序规则较为麻烦，直接修改库的排序规则。之后ok了。（注明：一开始一直把问题集中在ETL的转换上，实际上，ETL在抽取数据之后只不是乱码，则插入一般是没有问题的）

　　后来把这个成功导入数据的sqlserver A备份到其它sqlserver B。检查了目标库sqlserver B的默认排序规则和数据库的排序规则都是Chinese_PRC_CI_AS，都没有问题，不管使用ssis的平面文件，还是数据库源导入数据到sqlserverB都不行。始终报诸如“由于为列“UUID”指定了多个代码页(936 和 1252)，无法处理此列。”这样的问题。最后把问题定位到列的排序方式。发现原来是在sqlserverB的列都SQL_Latin1_General_CP1_CI_AI。起因可能是起初导数据或者ETL帮助生成表的时候，导致这个列排序为SQL_Latin1_General_CP1_CI_AI。删除表，通过ssis数据源导入数据到sqlserverB，自动创建表（注意，有些时候还是需要手动修改一个生成表的sql）。此时的列的排序方式正确，只要sqlserver到sqlserver导数据不报错，我想应该导入的也是正确的中文。经检验，中文正常，我想通过ETL肯定也会正常。这里不在检验。

 

结论：

　　了解sqlserver的排序规则的功能和意义，以及分类，主要是中文和非中文的问题。选择正常的排序规则，相应的文字才会正确。

　　有三个级别的排序规则的设定。安装数据库的时候，创建数据库的时候，创建表的时候（列）。

 

此外另一个问题：

　　ETL在插入时间数据到sqlserver时，有时会遇到“Only dates between January 1, 1753 and December 31, 9999 are accepted.”的问题，但是sqlserver中的目标列已经设置成了datetime2(7)了。是可以容纳这个范围之外的数据，datetime确实只允许在这个范围。因此ETL中相关的功能没有对新版的sqlserver类型的支持。

解决办法：

　　sqlserver在向时间字段插入数据时，如果源字段是varchar型，则会自动转换，此时，只需要把源端的时间转换为比较标准的时间字符串即（sqlserver可以自动识别的），比如oracle就可以使用to_char(xx,'yyyy-MM-dd HH24:mi:ss')。即可以解决问题。但是要注意，目标字段的类型要使用datetime2(7)。

 相关查询语句：

如下来自http://blog.csdn.net/delphigbg/article/details/12744807的对排序规则的介绍。原文还有对这三种情况的修改方法。

 oracle查询编码方式：
select userenv('language') from dual;
Select * From Nls_Database_Parameters
sqlserver查询语句：
 系统默认排序规则： SELECT SERVERPROPERTY(N'Collation')
 数据库排序规则： SELECT DATABASEPROPERTYEX('DB_JS_QZK', 'Collation')。

什么是排序规则呢？ 排序规则根据特定语言和区域设置标准指定对字符串数据进行排序和比较的规则。SQL Server 支持在单个数据库中存储具有不同排序规则的对象。MSDN解释：在 Microsoft SQL Server  中，字符串的物理存储由排序规则控制。排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则

当 Transact-SQL 语句在具有不同排序规则设置的不同数据库上下文中运行时，其运行结果可能会不同。如果可能，请为您的组织使用标准化排序规则。这样就不必显式指定每个字符或 Unicode 表达式中的排序规则。如果必须使用具有不同排序规则和代码页设置的对象，请对查询进行编码，以考虑排序规则的优先顺序规则。

排序规则指定了表示每个字符的位模式。它还指定了用于排序和比较字符的规则。排序规则的特征是区分语言、区分大小写、区分重音、区分假名以及区分全半角。如下所示：

Chinese_PRC_CI_AS   前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则，CI表示不区分大小写，AS表示区分重音。