Sql Server 2008 - Difference between collation types?

The letters ÅÄÖ/åäö do not mix up with A and O just by setting the collation to AI (Accent Insensitive). That is however true for â and other "combinations" not part of the Swedish alphabet as individual letters. â will mix or not mix depending of the setting in question.

The letters ÅÄÖ/åäö do not mix up with A and O just by setting the collation to AI (Accent Insensitive). That is however true for â and other "combinations" not part of the Swedish alphabet as individual letters. â will mix or not mix depending of the setting in question.

Since I have a lot of old databases I still need to communicate with, also using linked servers, I chose FINNISH _SWEDISH _CI _AS now that I'm installing SQL2008. That was the default setting for FINNISH _SWEDISH when the Windows collations first appeared in SQL Server.

The _100 indicates a collation sequence new in SQL Server 2008, those with _90 are for 2005 and those with no suffix are 2000. I don't know what the differences are, and can't find any documentation. Unless you are doing linked server queries to another SQL server of a different version, I'd be tempted to go with the _100 one.

Sorry I can't help with the differences.

Ok, thanks for the info. I've decided to go for the collation "Finnish_Swedish_100_CI_AS" as the database will be used with a new application beeing developed. – Octadrone Feb 11 '09 at 2:32.

Please view my blog at blogs.msdn.com/qingsongyao/. Please send my comments and feedbacks if you have more question related to collation.

1 Thanks Qingsong! I've just taken a quick look on your blog, it sure looks like interesting reading! – Octadrone Mar 26 '09 at 5:47 Thx, it was definitely helpful reading blogs.msdn.Com/b/qingsongyao/archive/2008/08/25/… – nubm Dec 30 '10 at 18:01.

Use the query below to try it out yourself. As you can see, å, ä, etc. Do not count as accented characters, and are sorted according to the Swedish alphabet when using the Finnish/Swedish collation. However, the accents are only considered if you use the AS collation.

For the AI collation, their order is unchanged, as if there was no accent at all. CREATE TABLE #Test ( Number int identity, Value nvarchar(20) NOT NULL ); GO INSERT INTO #Test VALUES ('à á'); INSERT INTO #Test VALUES ('áa'); INSERT INTO #Test VALUES ('aa'); INSERT INTO #Test VALUES ('aà '); INSERT INTO #Test VALUES ('áb'); INSERT INTO #Test VALUES ('ab'); -- w is considered an accented version of v INSERT INTO #Test VALUES ('wa'); INSERT INTO #Test VALUES ('va'); INSERT INTO #Test VALUES ('zz'); INSERT INTO #Test VALUES ('åä'); GO SELECT Number, Value FROM #Test ORDER BY Value COLLATE Finnish_Swedish_CI_AS; SELECT Number, Value FROM #Test ORDER BY Value COLLATE Finnish_Swedish_CI_AI; GO DROP TABLE #Test; GO.

To adress your question 1. Accent sensitive is a good thing to have enabled for Finnish-Swedish. Otherwise your "å"s and "ä"s will be sorted as "a"s and "ö"s as "o"s.(Assuming you will be using those kind of international characters).

More here: msdn.microsoft.com/en-us/library/ms14351... (discusses both binary codepoint and accent sensitivity).

Ah, ok! That was quite good to know. Thanks!

:) – Octadrone Feb 10 '09 at 8:51 @Octadrone: Since you probably can tell: What is the expected sort order for accented characters in Sweden? Does "å" sort separately or does it mix up with other forms of the letter "a"? – Tomalak Feb 10 '09 at 9:13 The expected sort order is ... x y z å ä ö.

All different. However, 'v' and 'w' are sorted as the same letter. – Jonas Lincoln Feb 10 '09 at 9:14 1 -1: Not true, rings and umlauts are not considered accents in the Finnish/Swedish collation.

– Blixt Feb 10 '09 at 10:38.

To address question 3 (info taken off the MSDN; wording theirs, format mine): Binary (_BIN): Sorts and compares data in SQL Server tables based on the bit patterns defined for each character. Binary sort order is case-sensitive and accent-sensitive. Binary is also the fastest sorting order.

If this option is not selected, SQL Server follows sorting and comparison rules as defined in dictionaries for the associated language or alphabet. Binary-code point (_BIN2): For Unicode data: Sorts and compares data in SQL Server tables based on Unicode code points. For non-Unicode data: will use comparisons identical to binary sorts.

The advantage of using a Binary-code point sort order is that no data resorting is required in applications that compare sorted SQL Server data. As a result, a Binary-code point sort order provides simpler application development and possible performance increases. For more information, see Guidelines for Using BIN and BIN2 Collations.

On Questions 2 and 3 Accent Sensitivity is something I would suggest turning OFF if you are accepting user data, and ON if you have clean, sanitized data. Not being Finnish myself, I don't know how many words there are that are different depending on the ó ô õ or ö that they have in them. But if there are users entering data, you can be sure that they will NOT be consistent in their usage, and you want to be able to match them.

If you are gathering data from a dataset that you know the content of, and know the consistency of, then you will want to turn Accent Sensitivity ON because you know that the differences are purposeful. The same questions apply when considering Question 3. (I'm mostly getting this from the link Tomalak provided) If the data is case and accent sensitive, then you want _BIN, because it will sort faster.

If the data is irregular, and not case/accent sensitive, then you will want _BIN2, because it is designed for Unicode data.

Being swedish myself I could inform you that the letters åäö is very often used in our language. So in most cases you problable want to be able to sort them correctly. – Octadrone Feb 11 '09 at 2:27 I apologize for my ignorance, however, if the 'ö' is not available, would you simply use an 'o' or does that completely change the word?

– DevinB Feb 11 '09 at 13:08 In most cases it will just create a word that really just mean anything with an ö instead of o. If that also is used in a context I believe Swedes would have no problems of understanding the meaning. Though, it would be expected by users to be able to use åäö in a swedish system.

:) – Octadrone Mar 27 '09 at 17:56.

To address qestion 2: Yes, if accent's are required grammer for the given language.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.