Hi, 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..
Thanks!
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.