sqlserver排序规则是否意味着列名必须大小写正确？以及如何处理 [英] Does sqlserver collation mean column names must be correct case? And how to deal with that

问题描述

在SQL Server（2000或2005）中，是否可以设置数据库或服务器归类，以便标识符名称（表，列等）的大小写正确？如果是这样，是对所有区分大小写的排序规则都适用吗，还是它是一个单独的设置？ （我一直认为区分大小写适用于数据，而不适用于对象名称。）

In SQL Server (2000 or 2005) is it possible to set the database or server collation so that identifier names (tables, columns, etc) need to be in the correct case? If so, is that true of all case-sensitive collations or is it a separate setting? (I've always thought of case-sensitivity applying to data, not to names of objects).

如果存储的过程和查询不存在，这可能会破坏应用程序。写的大小写一致吗？有没有一种方法可以不必确保所有查询都使用正确的大小写（例如设置数据库连接的排序规则）来处理此问题？

Presumably this would break an application if its stored procs and queries weren't written with consistent case? Is there a way to deal with this without having to ensure all queries use the correct case, such as setting the collation of a database connection?

从拥有一个可能带有不一致大小写sql代码的现有应用程序的角度来看，我希望能够针对具有不同归类的数据库运行该应用程序。我需要什么设置，或者我不能与应用程序一起使用哪些数据库/服务器排序规则集？

I'm looking at this from the point of view of having an existing application which probably has inconsistently cased sql code in it, and I'm wanting to be able to run it against databases with different collations. What settings would I need or what set of database/server collations could I not use the application with?

推荐答案

排序规则是确定查询是否区分大小写的因素。因此，确保您的架构可在多种环境下工作的唯一方法是使查询区分大小写。如果查询不一致，则排序规则必须不区分大小写，否则将不起作用。

The collation is what determines if your queries will be case insensitive. So the only way to ensure that your schema will work against multiple environments is to have your queries be case sensitive. If your queries are not consistent, then your collation MUST be case insensitive otherwise it will not work.

http://msdn.microsoft.com/zh-cn/library/aa174903（SQL.80）.aspx

要注意的一件事是，一旦使用特定的排序规则设置了SQL Server环境，就无法在不创建新SQL Server实例的情况下对其进行更改。因此，不区分大小写通常是要走的路。

One thing to note is that once you've set up your SQL Server environment with a certain collation, you CANNOT change it without creating a NEW SQL Server instance. So Case-Insensitive is usually the way to go. And then strive to have consistency in your queries.

我相信一旦设置了排序规则，它将同时适用于数据和元数据。

Once a collation is set it applies to both data and metadata, I believe.

这篇关于sqlserver排序规则是否意味着列名必须大小写正确？以及如何处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！