Changing SQL Server collation to case insensitive from case sensitive?

Question

I've recently installed SQL Server 2008 and I selected collation as case sensitive. I want to make it case insensitive for the entire instance (not for a database in that instance). If I change the collation does it affect any existing databases? if so in what way?

Answer 1

You basically need to run the installation again to rebuild the master database with the new collation. You cannot change the entire server's collation any other way.

See:

• MSDN: Setting and changing the server collation
• How to change database or server collation (in the middle of the page)

Update: if you want to change the collation of a database, you can get the current collation using this snippet of T-SQL:

SELECT name, collation_name 
FROM sys.databases
WHERE name = 'test2'   -- put your database name here

This will yield a value something like:

Latin1_General_CI_AS

The _CI means "case insensitive" - if you want case-sensitive, use _CS in its place:

Latin1_General_CS_AS

So your T-SQL command would be:

ALTER DATABASE test2 -- put your database name here
   COLLATE Latin1_General_CS_AS   -- replace with whatever collation you need

You can get a list of all available collations on the server using:

SELECT * FROM ::fn_helpcollations()

You can see the server's current collation using:

SELECT SERVERPROPERTY ('Collation')