SQL Server 中区分大小写的变量 [英] Case sensitive variables in SQL Server

问题描述

我有一个包含多个数据库的 SQL Server.我希望这些数据库之一对变量名称区分大小写，而其他数据库则不区分大小写.

I have a SQL Server with several databases. I want for one of these databases to be case sensitive to names of variable and for others to not.

我需要更改设置，以便我可以毫无错误地运行此脚本

I need to change settings so I could run this script without errors

DECLARE @A int
DECLARE @a int

如何将一个数据库更改为区分大小写(对于其变量名称)，同时允许实例上的其他数据库对变量名称不区分大小写?

How can you change one database to be case sensitive (for its variable names), while allowing other databases on the instance to be case insensitive for variable names?

推荐答案

您需要将服务器排序规则更改为区分大小写以获得所需的行为.仅仅改变数据库的排序规则是不够的.

You need to change the server collation to case sensitive to get the behavior you want. Just changing the collation for the db is not enough.

SQL Server 安装的默认排序规则，SQL_Latin1_General_CP1_CI_AS 不区分大小写.

The default collation of a SQL Server installation, SQL_Latin1_General_CP1_CI_AS is not case sensitive.

听起来您想将服务器的排序规则修改为不区分大小写的排序规则.选择带有 _CS 的一个._CI 的意思是不区分大小写"，区分​​大小写的是_CS.也许你会想要Latin1_General_CS_AS.

It sounds like you want to modify the collation of your server to one that is case-insensitive. Choose one with _CS. The _CI means "case insensitive", and case-sensitive is _CS. Maybe you'll want Latin1_General_CS_AS.

这是对 将 SQL Server 排序规则从区分大小写更改为不区分大小写?.

来自 SQL Server 联机丛书:

标识符的排序规则取决于定义它的级别.

COLLATE (Transact-SQL)

The collation of an identifier depends on the level at which it is defined.

• 实例级对象的标识符，例如登录名和数据库名称，被分配了实例的默认排序规则.

• 数据库中对象的标识符，例如表、视图和列名，被分配了数据库的默认排序规则.

• Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance.

• Identifiers of objects within a database, such as tables, views, and column names, are assigned the default collation of the database.

例如，两个名称仅大小写不同的表可以在区分大小写的数据库中创建，但不能在不区分大小写的数据库中创建.有关详细信息，请参阅数据库标识符.

For example, two tables with names different only in case may be created in a database with case-sensitive collation, but may not be created in a database with case-insensitive collation. For more information, see Database Identifiers.

变量、GOTO标签、临时存储过程和临时表的标识符是在服务器实例的默认排序规则中.

The identifiers for variables, GOTO labels, temporary stored procedures, and temporary tables are in the default collation of the server instance.

当连接上下文与一个数据库相关联时，可以创建变量、GOTO 标签、临时存储过程和临时表，然后在上下文切换到另一个数据库时引用.

Variables, GOTO labels, temporary stored procedures, and temporary tables can be created when the connection context is associated with one database, and then referenced when the context has been switched to another database.

您可以使用以下方法检查您的服务器排序规则:

You can check your server collation using:

SELECT SERVERPROPERTY('collation');

SQL_Latin1_General_CP1_CI_AS
(1 row(s) affected)

这篇关于SQL Server 中区分大小写的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！