best default collation of a multilingual database

Question

I am a bit confused about de default collations when creating a database. The data stored in the database will be in different languages. The main users of the database will be using the spanish language, but it will also be used in english, french... As the spanish default collation is Modern_Spanish_CI_AS, and the english, french italian.. defaults to Latin1_General_CI_AS, I would like to be advised on which collation to use, and if there are some drawbacks about using one collation or the other.

Many thanks for your help Regards

Javier

Answer 1

A collation has two effects:

1. For non-Unicode data types it determines the code page of the data, i.e. it determines which characters you can store in the column/variable or not
2. For all data types, it affects how data is sorted and compared, i.e. ORDER BY and equality

To avoid problems with the first issue, always store and manipulate Unicode data using the nchar/nvarchar data types, because then you don't have to worry about the collation anyway. It requires more disk space, but it avoids some really awkward issues, so for most people it's probably a good tradeoff.

For the second issue, use the collation that makes the most sense for your database, i.e. which collation sorts and compares the data in the way that you want to do it most of the time? For example, if you know that case-sensitive comparisons will be important then Latin1_General_CS_AS might be a better choice.

And you can always use COLLATE to specify the collation explicitly if you need more control over specific queries:

create table #t (name nvarchar(100))

insert into #t select N'Che'
insert into #t select N'Carlos'
insert into #t select N'Cruz'

select name from #t order by name collate Modern_Spanish_CI_AS
select name from #t order by name collate Traditional_Spanish_CI_AS

drop table #t

If you don't know how text data will be sorted or compared and if your users don't know either, then I would just stay with your default collation (and use Unicode!); in the worst case, you can always move the data to a new table with the correct collation. And there's a lot of documentation on collations in Books Online that you should have a look into.