Questions about Accent Insensitivity in SQL Server (Latin1_General_CI_AS)

Question

All of our databases were installed using the default mapping ( Latin1_General_CI_AS ).

We plan to change the sorting so that customers can search for a database with accent insensitive.

Questions:

• What are the negatives (if any) of having a database that ignores accents?

• Is there a performance overhead for an accent-free database?

• Why the default value for SQL Server collation sensitivity is the default; why does anyone want emphasis sensitive by default?

Answer 1

Seriously, changing database sorting is a pain in the kingdom. Check out this HOWTO from codeproject and then think before you do it! This is an EASY way!

• http://www.codeproject.com/Articles/302405/The-Easy-way-of-changing-Collation-of-all-Database

First, you can enable accent-insensitive database searches by simply specifying that you don’t have to change the sort as part of the search.

  select * from TableName where name collate Latin1_General_CI_AI like @parameter 

Plain. However, this will damage the indexes.

An alternative is to provide a computed field that you can index separately.

  create table TableName( ix int identity primary key, name nvarchar(20) collate latin1_general_ci_as ) go alter table TableName add name_AI as name collate latin1_general_CI_AI go create index IX_TableName_name_AI on dbo.TableName(name_AI) 

The above example puts it in a table, but you can also create an indexed view.

  create view dbo.TableName_AI with schemabinding as select ix, name collate Latin1_general_CI_AI as name from dbo.TableName go -- Need a unique clustered index first create unique clustered index IX_TableName_AI_Clustered on dbo.TableName_AI(ix) -- then the index for searching create index IX_TableName_AI_name on dbo.TableName_AI(name) 

Then, to search without accents, use the TableName_AI view.

To answer your specific questions:

• In a database that does not include accents, accent-sensitive searches will be slower.

• Yes, but not so, you would have noticed

• It is just that. Something should be the default: if you do not like it, do not use the default value!

  Think of it this way: “Hard” and “Herd” are not the same word. This difference in vowels is enough - even if they sound the same.

  The difference in emphasis (a vs. á) is somewhere between the difference in cases (A vs. a) and the difference in letters (a vs e). You have to draw a line somewhere.

  The actent affects the sound of a word and can make it have a different meaning, although I try my best to think about examples. I guess this makes sense for those who have words in their database in a language that uses accents.