Compartir a través de

Changing collation of a column is not so trival

  • Artículo

Several days ago, one customer asked me a question about collation. Here is the question:

     My applications requirement to search English characters along with accent characters like (å, Å), If we search Name “James “ by my application it should return both names “James” and “Jåmes”.

      We are using SQL_Latin1_General_CP1_CI_AS Collation after doing some analysis we found if we change this SQL_Latin1_General_CP1_CI_AS Collation by SQL_Latin1_General_CP1_CI_AI than my query returning desire results. Here I am trying to make it Accent Insensitive.

     But here I want to know whether it would work fine, Is there any problem to change the collation order in production environment. Or is there any way to enhance search functionality including accent characters. For your information we are using nvarchar data type to store the data. Can you tell me which collation we can use or any other way can we enhance search capability.

Here is the reply from me:

    First, want to know whether you always want assent insensitive behavior for that column. If you only want one particular query return assent insensitive behavior, then you can rewrite the query use “collate” clause, such as select … where name like ‘James’ collate SQL_Latin_General_CP1_CI_AI. Of course, you will not be able to use any indices defined on the name column then.

  Second, I like to let know the impact of changing a collation. We use collation for string sorting and comparison. There are some cases, the comparison/sorting result is persistent in the database. Examples are: indexes on string column, persistent computed column involving string comparison, constraints involving string comparison, foreign key/primary key, etc. That is the reason that BOL at https://msdn.microsoft.com/en-us/library/ms190920.aspx says:

You cannot change the collation of a column that is currently referenced by any one of the following:

·         A computed column

·         An index

·         Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

·         A CHECK constraint

·         A FOREIGN KEY constraint

   Third, I like to let you know that the impact after you change the collation to a new collation: columns with different collations cannot compare directly, so that if the column is also join/compare with other columns, you will have collation incompatible error.

    In summary, changing collation in a product environment is not so trivial; I DO suggest you test it in your test environment. Also, I think always aligning your column collation with your database collation is a better, i.e., your column collation is the same as your database collation.

Comments