How to remove Case Sensitive check in SQL Server 2008?

Question

I just installed SQL Server 2008 and imported AdventureWorksDatabase (for SQL 2005, as for 2008 that didn't worked).

It is now enforcing case sensitivity when I searched for any tables, views etc Hence Person.contact table when written throws an error of Invalid column name, but when Person.Contact is written it shows all rows.

Plus the intellisense too doesn't work great.

Answer 1

Case sensitivity is controlled by the collation the database uses - check this by querying the system catalog views:

select name, collation_name 
from sys.databases

A collation name will be something like: Latin1_General_CI_AS

The _CI_ part is telling me here that it's a case insensitive collation. If you see a _CS_ then it's a case sensitive collation.

You can change a database's default collation using:

ALTER DATABASE AdventureWorks COLLATE ....... 

and pick any valid collation here - use one with a _CI_ to get a case-insensitive collation.

Trouble is: even if you change the collation on the database level, certain tables might still have individual column that had a specific collation defined when the table was created. You could also change all of these, but that's going to be a bigger undertaking. See this article for more info and a script to check and possibly change individual columns in your tables.

The reason the intellisense might not be working properly is that the case sensitivity of database objects per se is controlled by the server collation - which can again be different from any database default.

To find out what the server's collation is, use:

SELECT SERVERPROPERTY('Collation')

Changing the server's system collation is quite a messy process and requires you to use the original setup.exe as explained here.

Answer 2

The problem here is Case Sensitivity of the table name Contact. You should set collation_name of the Database AdventureWorks as Latin1_General_CI_AS

Check for collation_name:

SELECT name, collation_name
FROM sys.databases
WHERE name = 'AdventureWorks';
GO

If the collation_name is Latin1_General_BIN or Latin1_General_CS_AS change it to Latin1_General_CI_AS

ALTER DATABASE AdventureWorks
COLLATE Latin1_General_CI_AS ;
GO

If the Database has locked to do this action "The database could not be exclusively locked to perform the operation." . Alter the Database to Single User

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

and do

ALTER DATABASE AdventureWorks
COLLATE Latin1_General_CI_AS ;
GO

Revert back the Database to Multi User finally

ALTER DATABASE AdventureWorks SET MULTI_USER WITH ROLLBACK IMMEDIATE

Or

You can change the Collation in Database Properties.