预期在开发和生产SQL Server之间有不同的排序规则有什么问题？ [英] What issues to anticipate having different collations between development and production SQL Servers?

问题描述

请问无法更新sys.columns - 任何其他方法？ 模糊地提到使用不同的排序规则部署到服务器时出现的问题。

Subquestioning "Unable to update sys.columns - any other approach?" vaguely mentioning the problems on deployment to server with a different collation.

问题是，默认情况下，SQL Server排序规则是由Windows区域和语言选项确定（在设置期间） - >高级 - >选择一种语言来匹配语言版本的非Unicode程序要使用在控制面板中。

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.

因此，开发人员和生产/客户端SQL服务器之间的排序规则的差异是很常见的情况。

例如，我有Cyrillic_General_CI_AS排序规则来自Windows在我的本地SQL Server和我有客户端具有服务器与希伯来语整理。

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.

因此，开发人员需要使用一个排序规则开发什么问题/问题，有时候甚至不知道客户端/客户在SQL Server上有哪个排序规则？

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?

更新：

我们说典型的情况是我不从头开发数据库或安装生产SQL Server在客户端。典型的情况是我连接/部署到共享或专用服务器上的SQL Server和/或接收数据库的备份。

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机器在en-us Windows XP Pro SP3（英语）和en-us SQL Server（英语）上运行。

Plz看到我从SQL Server的默认排序来自哪里的问题（选择语言匹配您要使用的非Unicode程序的语言版本）。

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").

我不明白一个从不使用临时表或任何使用TEMPd的东西。

TempDB用于SQL Server中的大多数操作，甚至用于存储来自选择的中间结果。

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

我不觉得CS_AS很尴尬。恕我直言。它是CI-AI是尴尬。

@Damien_The_Unbeliever,
I do not feel that CS_AS is awkward. IMHO. it is CI-AI that is awkward.

如果对脚本（手动或通过SSMS），我不可避免地碰撞
批注是针对所有列或无脚本的

If to script (by hand or by SSMS) then I inevitably bump against Collations are scripted either for all columns or for none

Update3：

我的问题明确说，我的更新重申，我不使用任何斯拉夫语排序规则），但是我的SQL Server和数据库有Cyrillic_General_CI_AS作为默认排序规则，因为SQL Server默认设置链接到Windows配置上的依赖。

这里的理由是，BTW，很难解释或理解，但这是在SQL Server设置中简单的状态...

但是结果是这种依赖性实际上确保不同国家的SQL Server的不同（默认）排序。这是这个问题的本质...

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...

推荐答案

如果您的客户没有希伯来语的SQL服务器，从头开始安装，则SQL Server排序规则可在安装期间由DBA配置，方法是选择自定义安装选项;你最终会在2005/8获得一个排序规则设计器选项卡。

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.

如果它太晚了，那么如果你从来没有使用临时表或其他使用TEMPdb，或者你都是unicode或者如果你有幸运的SQL逻辑，将所有的排序规则运行回来，我认为你不需要担心它。假设不是这样（因为它们在我们的数据库中），那么您将需要在SQL服务器上的本地排序规则中运行数据库，以避免排序规则冲突。为此，您需要使用排序规则设置获取SQL脚本，并将其运行到新服务器中，以便选择本地排序规则。

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.

最后需要担心的是，如果您需要将数据从客户端返回到运行西里尔字符的本地系统，您将需要一个希伯来语整理服务器或DTS / SSIS将备份中的所有数据导入您办公室的本地归类副本以运行数据。

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.

这篇关于预期在开发和生产SQL Server之间有不同的排序规则有什么问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！