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

User · Answer

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.

User · Answer

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

[sql-server] Changing SQL Server collation to case insensitive from case sensitive?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to collation