How to disable case sensitivity in SQL Server 2012?

Question

I tried to update collation_name in sys.databases. I am trying to change 'SQL_Latin1_General_CP1_CS_AS' to 'SQL_Latin1_General_CP1_CI_AS' in order to disable case sensitivity in table.

update d set d.collation_name='SQL_Latin1_General_CP1_CI_AS'
--SELECT name, collation_name 
FROM sys.databases d
WHERE name = 'db_name' 

However, I'm getting this error:

Msg 259, Level 16, State 1, Line 1
Ad hoc updates to system catalogs are not allowed.

• mustaccio: So, what seems unclear in the error message?
• Solomon Rutzky: Are you wanting to change the collation for all queries or just one field in one table?
• Aaron Bertrand: If you and @Biplove are the same person, please merge your accounts.

Answer 1

First, as you have now seen, you cannot directly alter meta-data in the system views. However, you could change the setting for a particular database using ALTER DATABASE:

ALTER DATABASE { database_name | CURRENT }
 COLLATE collation_name;

Please note that the option to use the CURRENT keyword was introduced in SQL Server 2012.

OR, if you only want to change how a particular field in a particular table behaves for all queries (well, all that do not override the fields collation with the COLLATE clause -- see notes below), then you can do that by changing the collation for just that one field:

ALTER TABLE [TableName]
 ALTER COLUMN [ColumnName] NVARCHAR(10)
 COLLATE Latin1_General_100_CI_AS
 NOT NULL;

Just be sure to specify the exact same datatype and NULL vs NOT NULL setting that the field currently has!!

Now, a few notes about what you are trying to accomplish:

• The collations starting with SQL_ are deprecated. You should use the Windows Collations. In this case, you would use Latin1_General_100_CI_AS. The _100 series are the newest versions and were introduced in SQL Server 2008.

• Changing the default collation of a database does not change the collation for any string fields in any existing tables. It only affects operations involving string literals and variables that do not also involve a field in a table that would already have a collation specified. For more details about the ramifications of changing a database's collation when tables already exist (with example code showing what the true effects are, including potential errors if joining existing and new tables on string fields), please see my answer here: Latin1_General_BIN performance impact when changing the database default collation

• Any string operation, whether it involves literals, variables, or fields in tables, can have the default collation overridden by using the COLLATE clause. For example:

  SELECT *
  FROM dbo.SomeTable st
  WHERE st.SomeField = @StoredProcedureParameter COLLATE Latin1_General_100_CI_AS;
  

  If you are only concerned about some (not all) queries on some (not all) tables, then this is a great place to start.

• The collation actually used for a string operation is determined by looking at the various levels of where to specify a collation. Please see Collation Precedence for more info.

• Aaron Bertrand: If the problem is widespread and the schema or codebase are very large, it is sometimes cleaner to create a new database, build new tables, and migrate the data. Especially if the old database using a collation that differs from the server collation, and the new collation matches.