开发和生产 SQL Server 之间使用不同的排序规则会出现哪些问题？
质疑“无法更新 sys.columns - 还有其他方法吗？” 含糊地提到部署到具有不同排序规则的服务器时的问题。
问题是默认情况下 SQL Server 排序规则是由 Windows 区域和语言选项（在安装过程中）确定的 -->进阶-->在控制面板中“选择一种语言以匹配您要使用的非 Unicode 程序的语言版本”。
因此，开发和生产/客户端 SQL Server 之间的排序规则差异是很常见的情况。
例如，但仅作为示例，我的本地 SQL Server 上有来自 Windows 的 Cyrillic_General_CI_AS 排序规则，并且我的客户端拥有具有希伯来语排序规则的服务器。
那么，开发人员应该预见到哪些问题/问题必须使用一种排序规则进行开发，有时甚至可能不知道客户端/客户在 SQL Server 上使用哪种排序规则？
更新：
假设典型的情况是我不从头开始开发数据库或在客户端安装生产 SQL Server。典型的情况是我连接/部署到共享或专用服务器上的 SQL Server 和/或接收数据库备份。
更新2：
@u07ch，
我的开发计算机在 en-us Windows XP Pro SP3（英文）和 en-us SQL Server（英文）上运行。
请从 SQL Server 的默认排序规则的来源中查看我的问题（“选择一种语言来匹配您要使用的非 Unicode 程序的语言版本”）。
我无法理解“永远不要使用临时表或任何其他使用 TEMPd 的东西”。
TempDB 用于 SQL Server 中的大多数操作，甚至用于存储选择的中间结果。
@Damien_The_Unknowner，
我不觉得CS_AS有什么尴尬。恕我直言。尴尬的是CI-AI。
如果要编写脚本（手动或通过 SSMS）那么我不可避免地会遇到 排序规则要么为所有列编写脚本，要么不为任何列编写
更新3：
我的问题明确指出，并且我的更新重申，我无论如何都不会使用任何西里尔字母排序规则（甚至代码页），但由于 SQL Server 默认设置链接，我的 SQL Server 和数据库将 Cyrillic_General_CI_AS 作为默认排序规则对 Windows 配置的依赖。
顺便说一句，其基本原理很难解释或理解，但这只是 SQL Server 设置的现状......
但结果是这种依赖性实际上确保了不同国家的 SQL Server 具有不同的（默认）排序规则。这就是这个问题的本质...
Subquestioning "Unable to update sys.columns - any other approach?" vaguely mentioning the problems on deployment to server with a different collation.
The problem is that by default SQL Server collation is determined (during setup) by Windows Regional and Language Options --> Advanced --> "Select a language to match the language version of the non-Unicode program you want to use" in Control Panel.
So, this difference in collations between dev and production/client SQL Servers is quite common situation.
For example, but just for example, I am having Cyrillic_General_CI_AS collation having come from Windows at my local SQL Server and I have clients having servers with Hebrew collation.
So, what issues/problems should developer anticipate having to develop with one collation and possibly, sometimes, even not knowing which collation the client/customer is having on SQL Server?
Update:
Let's say that typical situation is that I do not develop the database from scratch or install production SQL Server at client. The typical situation is that I either connect/deploy to SQL Server on shared or dedicated server and/or receive backup of database.
Update2:
@u07ch,
my dev machine is running on en-us Windows XP Pro SP3 (English) and en-us SQL Server (in English).
Plz see my question from where default collation of SQL Server is coming from ("Select a language to match the language version of the non-Unicode program you want to use").
I could not understand how one "never use temporary tables or anything else that uses TEMPd".
TempDB is used for most operations in SQL Server, even for storing intermediate results from selects.
@Damien_The_Unbeliever,
I do not feel that CS_AS is awkward. IMHO. it is CI-AI that is awkward.
If to script (by hand or by SSMS) then I inevitably bump against
Collations are scripted either for all columns or for none
Update3:
My question explicitly says, and my updates reiterate, that I do NOT use any Cyrillic collations (or even codepages) in anyway but my SQL Server and databases have Cyrillic_General_CI_AS as default collation due to SQL Server default setup linked to dependences on Windows configuration.
The rationale under this is, BTW, difficult to explain or understand but this is simply status quo in SQL Server setups...
But the result is that this dependence practically ensures different (default) collations of SQL Servers in different countries. Which is the essence of this question...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果您的客户还没有希伯来语 SQL Server，并且您从头开始安装，则 DBA 在安装过程中可以通过选择自定义安装选项来配置 SQL Server 排序规则；您最终会在 2005/8 年度进入排序规则设计器选项卡。
如果为时已晚，那么如果您从不使用临时表或其他任何使用 TEMPdb 的东西，或者您都是 unicode，或者如果您有幸拥有 SQL 逻辑，可以在运行时将所有排序规则转换回来，那么我认为您不需要根本不用担心它。假设情况并非如此（因为它们在我们的数据库中），那么您将需要在 SQL Server 上以本地排序规则运行数据库，以避免排序规则冲突。为此，您需要使用排序规则设置取出 SQL 脚本，并将其运行到新服务器中，以便它选择本地排序规则。
最后要担心的是，如果您需要将数据从客户端返回到运行西里尔文的本地系统，您将需要一个希伯来语校对服务器，或者将 DTS/SSIS 中的所有数据从备份复制到您的本地校对副本中。办公室以便运行数据。
If your customers don't have a SQL server in hebrew already and you are installing from scratch then the SQL Server collation is configurable by the DBA during installation by selecting the custom install option; you eventually get to a collation designer tab in 2005/8.
If its too late for that then if you never use temporary tables or anything else that uses TEMPdb, or you are all unicode or if you are blessed with SQL logic that casts all the collations back as it runs then I think you won't need to worry about it at all. Assuming these aren't the case (as they are in our databases) then you will need to run the database in the local collation on the SQL server in order to avoid collation conflicts. To do that you need to get the SQL Script out with collation settings and run that into the new server so that it picks up the local collation.
The final thing to worry about is if you need to get the data back from the client to your local system running cyrillic you will either need a hebrew collation server or to DTS / SSIS all the data from a backup into a local collation copy in your offices in order to run the data.
如果您知道您将要处理不同的排序规则，那么我听到的最好的建议是确保您正在开发的排序规则尽可能地尴尬。因此，我会更改您的开发设置，至少使用区分大小写、区分重音的排序规则（CS_AS，而不是 CI_AS）。
这样，您在代码中所做的任何与排序规则相关的假设都应该显示在开发中，而不是发布到生产中时。
我还要确保所有脚本都是手工编写的（或者您关闭 SSMS 中包含排序规则的脚本选项），并且任何发布到生产环境的操作都是通过脚本完成的，而不是通过导入/导出向导或恢复数据库来完成。这样，生产数据库应该具有最终用户所需的排序规则选项。
If you know that you're going to be dealing with different collations, then the best piece of advice I've heard is to make sure that the collation you're developing with is as awkward as possible. So I'd change your development setup to use a case sensitive, accent sensitive collation (CS_AS, not CI_AS) at the very least.
This way, any collation related assumptions that you're making in your code should show up in development, rather than when releasing to production.
I'd also make sure that any scripts are written by hand (or you turn off the scripting options in SSMS that include collations), and any releases to production are done by script, rather than by e.g. Import/Export wizard or by restoring databases. That way, the production database should have the collation options desired by the end users.