Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 6 down vote accepted

The one without the code is a version 90 (SQL Server 2005) collation, used by previous version of Windows / SQL Server. 100 (SQL Server 2008) is newer, and 110 (SQL Server 2012) is newer still.

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 accurate with the 100 version (or 110, if it's available). However, if you've got any other SQL Servers running with French_CI_AS it might be better to keep it consistent with them.

share|improve this answer
    
We havent developed anything yet. Everything is fresh on SQL Server 2012. So i guest the best bet is to use the newest configurtion everywhere. –  Alexandre Jobin May 3 '13 at 15:18
2  
CI - Insensitive AS - Accent Sensitive –  user1207758 May 3 '13 at 18:31
    
I did not find any source that _110 collations exist. Only _80, _90 and _100 families exist (see e.g. msdn.microsoft.com/en-us/library/ms143726.aspx). –  Cheese 22 hours ago

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.