During the installation of SQL Server 2012, in the Collation tab, i can choose between French_CI_AS and French_100_CI_AS.
What do they mean and which one is newer? When there's no Code, is it newer or older than the 100?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
The one without the code is a version 90 (SQL Server 2005) collation, used by previous versions of Windows / SQL Server. The collation including 100 is newer (SQL Server 2008).
There's a bit of an overview of the differences in the SQL Server 2008 documentation, here, however it doesn't go into great detail.
If you're developing a new application, you may find that sorting is slightly more logical (particularly for unusual characters) with the 100 version. However, if you've got any other SQL Servers running with French_CI_AS, it might be better to keep it consistent with them.
SQL server must version the collations. Since collations determine sort order of data persisted in the database the collation must be guaranteed to remain stable between the releases. Otherwise a collation change (eg. fixing a bug in a collation) with a new release of Windows it would result in two rows int he database to sort differently before and after the release. This may sound trivial, but in effect it means is an index corruption because the items should be sorted as A > B (according to the new, fixed, collation rules) but in the database are present as B > A (according to the old, buggy, collation rules). Therefore SQL Server must snapshot the windows collation at each release and stick the release version number in the collation name (since the names do not have an explicit versioning scheme).
The collation you choose is subject to many factors. Consider that if you interact with an older server to exchange data it may not have the newest collation you use.