How to remove case sensitivity test in SQL Server 2008?

Question

I just installed SQL Server 2008 and imported AdventureWorksDatabase (for SQL 2005, since it didn’t work in 2008).

Now it applies case sensitivity when searching for any tables, views, etc. Therefore, the Person.contact table when writing generates an invalid column name error, but when Person.Contact is written, it displays all the rows.

Also, intellisense doesn't work fine either.

Answer 1

Case sensitivity is controlled by sorting the database — check this by querying the views of the system catalog:

select name, collation_name from sys.databases 

The sort name will look something like this: Latin1_General_CI_AS

The _CI_ part tells me that this is a case insensitive sort. If you see _CS_ , then this is case sensitive sorting.

You can change the default database setting using:

 ALTER DATABASE AdventureWorks COLLATE ....... 

and select any valid sort here - use one with _CI_ to get case insensitive sort.

Problem: even if you change the setting at the database level, some tables may still have a separate column that had the specific sorting defined when the table was created. You could also change all this, but it will be a larger event. See this article for more information and a script for checking and possibly changing individual columns in your tables.

The reason intellisense might not work properly is because the case sensitivity of database objects is itself controlled by server sorting, which can again be different from any default database.

To find out what server sorting is, use:

 SELECT SERVERPROPERTY('Collation') 

Changing the server system configuration is a rather messy process and requires that you use the original setup.exe as described here .

Answer 2

The problem here is the case sensitivity of the Contact table name. You must set collation_name in Database AdventureWorks as Latin1_General_CI_AS

Check assembly_name:

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

If 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 is locked to perform this action, "The database cannot be locked exclusively to complete the operation." . Database change for a single user

 ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

and do

 ALTER DATABASE AdventureWorks COLLATE Latin1_General_CI_AS ; GO 

Revert the database back to the multi-user system finally

 ALTER DATABASE AdventureWorks SET MULTI_USER WITH ROLLBACK IMMEDIATE 

Or

You can change the sort value in the database properties.