归类选择有什么区别？

本文是为“ MS SQL Server开发人员”课程的学生准备的
我想分享一个先前项目的故事，它说明必须非常仔细地选择Collat​​ion。 关于仍然错误地选择此参数会发生什么，以及存在解决问题的选项。

首先，简要介绍什么是归类。 在SQL Server中，“排序规则”参数告诉服务器如何对行进行排序和比较。 例如，“ Apple”和“ apple”行。 他们是否不同？ 这取决于指定的归类。 如果寄存器变得越来越不清楚，那么如何使用“圣诞树”和“圣诞树”示例？ 将它们视为相同还是不同？ 这也都在整理中。

这个故事发生在一个功能与DropBox或Google Drive非常相似的项目中。 它提供了在不同计算机上管理其同步文件夹和文件的能力，以及其他用户对该同步文件夹的访问权。

因此，故事始于以下事实：在Prod服务器上，日志中存在75-90％的错误（请参见下面的屏幕截图），并且不清楚它们来自何处，以及原因是什么。 错误是：“ ReadWrtLst未完成”。 接下来是用户的详细信息及其文件夹。

代码很快找到了一个产生错误的地方，但是我们不明白它为什么会发生以及如何重现它。很明显，错误与用户以某种方式设法做到这一点有关在您的操作系统中创建另一个具有相同名称的文件夹。

我们已收集了有关为其发出此错误的用户的信息。 在这里，我们面临着第一个惊喜：在系统的数百万用户中，只有50个出现此错误，而这50个用户生成了90％的错误日志。 由于无法再现这种情况，我们决定与其中一位用户联系，并找出为什么其中一个文件夹未与之同步。 在我们看来，该文件夹与其他文件夹相同，唯一的不同是，该文件夹是使用象形文字以用户的语言来调用的。 用户是日语。 顺便说一下，在这50个用户中，日语是大多数。

感谢团队的一名开发人员，我们得以重现该错误。 该错误是由于所选的排序规则，操作系统认为文件夹名称不同，而SQL Server认为它们相同。

项目中使用的整理：
SQL_Latin1_General_CP1_CI_AS

关于如何阅读归类的一个小题外话。 （如果您熟悉它，请随时跳过它。）

因此，归类包含以下几部分：

• SQL-通过SQL Server（排序规则开头的SQL）或Windows（当时只是Latin1_ ...）的排序选项；
• Latin1_General-使用的语言环境或语言；
• CP1-代码页-代码页；
• CI-不区分大小写-不区分大小写；
• AS-重音敏感-考虑到轴突或变音符号，换句话说，“ a”不等于“ấ”。

此排序规则曾经是安装SQL Server时的默认排序规则。

有哪些选择？

• _KS-考虑到平假名和片假名的日语字符，如果未选中该选项，则SQL Server将把平假名和片假名的象形文字解释为相同。
• _WS-考虑到字符的宽度，如果未选择该参数，则将“文本”和“ T ext”视为同一行。
• _VSS-考虑到选择日文拼写选项的迹象，该现象从2017年版本开始出现。
• _UTF8-允许您将数据存储在UTF8中。

数据库中的所有文本字段均使用NVARCHAR类型。
事实证明，由于当前的归类忽略了日语字符的拼写差异和字符宽度的差异，因此SQL Server不能以与操作系统相同的方式比较字符串，这导致了问题，即 用户可以创建文件夹，不能将其添加到系统中进行同步。 比较文件名时，以后会发生同样的事情。

我们开始考虑如何解决此问题并更改排序规则。

可以在多个级别上设置排序规则：

• SQL Server实例
• 资料库
• 桌子
• 领域

同时，不建议在数据库内部使用不同的归类，因为每次比较具有不同归类的行时，都需要使用COLLATE进行转换，并向服务器指示应使用哪个比较顺序。

在很明显没有正确选择排序规则的情况下，有哪些选项可用？

1. 在数据库级别更改排序规则；
2. 在字段级别更改归类（在我们的例子中，更改整个表没有意义）；
3. 添加Varbinary字段，并使用文件夹名称在该字段中写入一个与该字段重复的副本，并将其用于比较；
4. 告诉用户目录名称中的字符支持受到限制。

第一个选项-在数据库级别更改排序规则-是最困难的。 对于数据库，有必要重新创建数据库并在那里重新加载数据。 由于系统工作24/7，该选项立即被拒绝。

关于更改字段的第二个选项：最简单的实现方法是添加具有所需归类的字段，然后在其中传输数据。 但是，随后有必要更改与此字段配合使用的数据库中的代码，并且数据库中有很多代码。

我们最喜欢第三个选项，因为从理论上讲，它所做的更改最少，因为主字段将在当前的归类中继续存在，并且在转换时不会出现问题，而所有必需的功能都以日语字母或宽角色会工作。 缺点是有必要对软件部分进行更改，但是由于是服务器部分，因此可以做到这一点。

在这种情况下，第四个选项是最简单的，因为用户总数为几百万，只有50个用户有问题，但是，如果该应用程序在日本得到积极使用，则该解决方案将毫无用处。

将数据提供给管理层后，决定通知用户该软件不支持许多字符，并且当以同步文件和文件夹的名称使用该软件时，该软件可能无法正常工作。 这是一个临时解决方案，因为通过进一步分发，面临类似问题的用户数量将会增加，并且有必要使用前三个选项进行更改。

选择归类的最佳选择是基于您的应用程序需求。 如果您希望SQL Server以与OS相同的方式比较字符串，则默认情况下，归类绝对不正确。 不幸的是，在设计系统时，这样的细微差别很少在项目开始时就可见，但是，希望在阅读本文之后，您会回忆起所描述的情况，而不会自己踩踏。

有用的整理资源：
https://docs.microsoft.com/zh-cn/sql/relational-databases/collat​​ions/collat​​ion-and-unicode-support?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/sql-development/questions-sql-server-collat​​ions-shy-ask/
https://www.virtual-dba.com/sql-server-collat​​ion/