在表创建脚本中明确指定排序规则的优缺点是什么？ [英] What are the advantages and disadvantages of explicitly specifying collation in table creation scripts?

问题描述

创建表时，COLLATE子句是可选的。因此，在以下位置：

When creating a table, the COLLATE clause is optional. So, in:

CREATE TABLE T1 (
  F1 varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
  F2 varchar(50) NOT NULL
)

F2的排序规则由数据库默认值。

the collation of F2 will be determined by the database default.

过去，我们的团队始终使用默认排序规则。但是，我们现在有几列需要明确指定排序规则。我们正在考虑是否将我们的标准更改为始终指定排序规则，并寻找可能有助于此决定的其他信息。

In the past our team always used the default collation. However, we now have a few columns where we need to explicitly specify collation. We are considering whether to change our standard to always specify collation and looking for any additional information that may help in this decision.

因此：

• 始终指定排序规则的优点/缺点是什么？


• 我们是否需要掌握一些陷阱注意是否始终指定排序规则？

可能相关的附加信息：

• 我们部署到多个客户端，并非所有客户端都会同时升级。


• 某些客户端的服务器上确实有不同的整理配置。


• 我们的应用程序使用Ansi字符串，对Unicode的任何考虑都非常很远。


• 我们使用MS SQL Server 2005及更高版本（尽管我更愿意在支持排序规则的任何平台上普遍使用该问题）。

• We deploy to multiple clients, and not all clients will be upgraded at the same time.
• Some clients do have different collation configurations on their servers.
• Our applications use Ansi strings, and any consideration of Unicode is a very long way away.
• We use MS SQL Server 2005 and up (although I would prefer to keep the question general to any platform supporting collations if possible).

请注意：此问题不询问有关如何解决归类冲突或更改现有服务器/数据库/列归类的问题。 （已经有很多。）

Please Note: This question is not asking about how to resolve collation conflicts, or change existing server/database/column collations. (There are plenty of those already.)

推荐答案

您正在构建在客户端计算机上部署的应用程序。您应该对排序规则保持明确。

You are building an application that deploys on client machines. You should be explicit about your collations.

例如，默认排序规则不区分大小写。如果客户端（无论出于何种原因）倾向于将区分大小写作为其数据库中的默认值，则在不同的计算机上进行比较会有所不同。

For instance, the default collation is case insensitive. If the client -- for whatever reason -- prefers to have case sensitivity be the default in their database, then comparisons will differ on different machines.

您还会遇到问题调试。如果客户按一个顺序获得结果，则您的测试/开发系统可能以不同的顺序获得结果。这可能会妨碍调试和客户支持。

You also have the issue of debugging. If the client is getting results in one sequential order, your test/development system may get them in a different order. This can hamper debugging and customer support.

最后，最好是在调试，维护和支持方面拥有一致的软件。如果特定客户端需要其他格式的数据，请为该客户端自定义系统。

In the end, it is probably better to have consistent software on your side for debugging, maintenance, and support. If a particular client needs data in a different format, then customize the system for that client.

这篇关于在表创建脚本中明确指定排序规则的优缺点是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！