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?
This question is related to
sql-server
sql-server-2008
collation
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:
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')
You can do that but the changes will affect for new data that is inserted on the database. On the long run follow as suggested above.
Also there are certain tricks you can override the collation, such as parameters for stored procedures or functions, alias data types, and variables are assigned the default collation of the database. To change the collation of an alias type, you must drop the alias and re-create it.
You can override the default collation of a literal string by using the COLLATE clause. If you do not specify a collation, the literal is assigned the database default collation. You can use DATABASEPROPERTYEX to find the current collation of the database.
You can override the server, database, or column collation by specifying a collation in the ORDER BY clause of a SELECT statement.
Source: Stackoverflow.com