Collation-any practical difference?

Case:
*SQL Server 2005 Ent (9.00.3054.00)
*One database supporting the following countries:
*Austria
*Canada
*Denmark
*Finland
*France
*Germany
*Norway
*Spain
*Sweden
*Switzerland
*United Kingdom
*database and application is fully Unicode enabled
*server collation is Danish_Norwegian_CI_AS
*database collation is the same as server collation (Danish_Norwegian_CI_AS)
Question:
Will it be any major difference between Danish_Norwegian_CI_AS and
Latin1_General_CI_AS ? If any; do You have any good examples explaining the
differens(es)?
Both are Case insensitive and Accent sensitive (required) and both supports
all the 1252 character sets, but I have'nt been able to find any major
difference between the two in any documentation other than the first giving
the special "Nordic" characters æ,ø,å priority during compare and hence
sorting.
Any feedback would be highly welcome.ORDER BY is one thing to watch out for.
CREATE TABLE #t(c char(2))
INSERT INTO #t VALUES('a')
INSERT INTO #t VALUES('b')
INSERT INTO #t VALUES('z')
INSERT INTO #t VALUES('A')
INSERT INTO #t VALUES('B')
INSERT INTO #t VALUES('Z')
INSERT INTO #t VALUES('Ã¥')
INSERT INTO #t VALUES('ä')
INSERT INTO #t VALUES('ö')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('æ')
INSERT INTO #t VALUES('ø')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('ü')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('vb')
INSERT INTO #t VALUES('Va')
INSERT INTO #t VALUES('Wb')
INSERT INTO #t VALUES('wa')
SELECT * FROM #t ORDER BY c COLLATE Finnish_Swedish_CI_AI
SELECT * FROM #t ORDER BY c COLLATE Danish_Norwegian_CI_AS
SELECT * FROM #t ORDER BY c COLLATE Latin1_General_CI_AS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Back2Ax" <Back2Ax@.discussions.microsoft.com> wrote in message
news:E781A2FF-6446-4EC8-BBCD-08AEF1859079@.microsoft.com...
> Case:
> *SQL Server 2005 Ent (9.00.3054.00)
> *One database supporting the following countries:
> *Austria
> *Canada
> *Denmark
> *Finland
> *France
> *Germany
> *Norway
> *Spain
> *Sweden
> *Switzerland
> *United Kingdom
> *database and application is fully Unicode enabled
> *server collation is Danish_Norwegian_CI_AS
> *database collation is the same as server collation (Danish_Norwegian_CI_AS)
> Question:
> Will it be any major difference between Danish_Norwegian_CI_AS and
> Latin1_General_CI_AS ? If any; do You have any good examples explaining the
> differens(es)?
> Both are Case insensitive and Accent sensitive (required) and both supports
> all the 1252 character sets, but I have'nt been able to find any major
> difference between the two in any documentation other than the first giving
> the special "Nordic" characters æ,ø,å priority during compare and hence
> sorting.
> Any feedback would be highly welcome.|||Did the same test - can't say that the results where as expected in any way
:-( I expected that a,b,c would have been sorted the same way (rank) with all
collations but even this was different (c was returned before a in all
collations and the special Norwegian/Danish characters was'nt in the right
sequence either).
Should I make the decision based on the country with the most users or
should I take the number of special characters into consideration? There are
trade offs with every collation far as I can see.
Mayby I'm missing something here...
"Tibor Karaszi" wrote:
> ORDER BY is one thing to watch out for.
> CREATE TABLE #t(c char(2))
> INSERT INTO #t VALUES('a')
> INSERT INTO #t VALUES('b')
> INSERT INTO #t VALUES('z')
> INSERT INTO #t VALUES('A')
> INSERT INTO #t VALUES('B')
> INSERT INTO #t VALUES('Z')
> INSERT INTO #t VALUES('Ã¥')
> INSERT INTO #t VALUES('ä')
> INSERT INTO #t VALUES('ö')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('æ')
> INSERT INTO #t VALUES('ø')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('ü')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('vb')
> INSERT INTO #t VALUES('Va')
> INSERT INTO #t VALUES('Wb')
> INSERT INTO #t VALUES('wa')
>
> SELECT * FROM #t ORDER BY c COLLATE Finnish_Swedish_CI_AI
> SELECT * FROM #t ORDER BY c COLLATE Danish_Norwegian_CI_AS
> SELECT * FROM #t ORDER BY c COLLATE Latin1_General_CI_AS
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Back2Ax" <Back2Ax@.discussions.microsoft.com> wrote in message
> news:E781A2FF-6446-4EC8-BBCD-08AEF1859079@.microsoft.com...
> > Case:
> > *SQL Server 2005 Ent (9.00.3054.00)
> > *One database supporting the following countries:
> >
> > *Austria
> > *Canada
> > *Denmark
> > *Finland
> > *France
> > *Germany
> > *Norway
> > *Spain
> > *Sweden
> > *Switzerland
> > *United Kingdom
> >
> > *database and application is fully Unicode enabled
> > *server collation is Danish_Norwegian_CI_AS
> > *database collation is the same as server collation (Danish_Norwegian_CI_AS)
> >
> > Question:
> >
> > Will it be any major difference between Danish_Norwegian_CI_AS and
> > Latin1_General_CI_AS ? If any; do You have any good examples explaining the
> > differens(es)?
> >
> > Both are Case insensitive and Accent sensitive (required) and both supports
> > all the 1252 character sets, but I have'nt been able to find any major
> > difference between the two in any documentation other than the first giving
> > the special "Nordic" characters æ,ø,å priority during compare and hence
> > sorting.
> >
> > Any feedback would be highly welcome.
>