Sql-server – How to disable case sensitivity in SQL Server 2012

character-setcollationsql-server-2012unicode

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.

Best Answer

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.

Related Question