Case sensitive variables in SQL Server

Question

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?

Answer 1

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.

---

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

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.

This is a great answer to a previous question on Changing SQL Server collation to case insensitive from case sensitive?.

From the SQL Server Books Online:

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.

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

  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)