Swedish collation, 'V' = 'W' rule and different behaviour for VARCHAR and NVARCHAR
Alex Sadomov
There is a problem with sorting in db with SQL_SwedishStd_Pref_CP1_CI_AS
collation. As I'd known there is a specific rules in the swedish language,
one of which is 'V' = 'W' (this rule was revoked by swedish academy since
april 2006 - http://blogs.msdn.com/michkap/archive/2006/04/25/583307.aspx).
But SQL Server 2000 (and 2005) has different sorting behaviour for VARCHAR
and NVARCHAR data:
there is a table:
CREATE TABLE t_pdb_temp
(
col1 varchar (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL ,
col2 nvarchar (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL
)
and 4 rows in it:
Val Val
Wal Wal
Vel Vel
Wel Wel
In the first query we sort by varchar column, and in the second - by
nvarchar:
SELECT col1, col2
FROM t_pdb_temp
ORDER BY col1
SELECT col1, col2
FROM t_pdb_temp
ORDER BY col2
And as a result we have different record sets accordingly:
Val Val
Vel Vel
Wal Wal
Wel Wel
---------
Val Val
Wal Wal
Vel Vel
Wel Wel
As you can see, 'V'='W' rule is working only for NVARCHAR column. Can
anybody explain this behaviour?
Erland Sommarskog
> There is a problem with sorting in db with SQL_SwedishStd_Pref_CP1_CI_AS
> collation. As I'd known there is a specific rules in the swedish language,
> one of which is 'V' = 'W' (this rule was revoked by swedish academy since
> april 2006 -
> and NVARCHAR data:
> Val Val
> Vel Vel
> Wal Wal
> Wel Wel
> ---------
> Val Val
> Wal Wal
> Vel Vel
> Wel Wel
>
> As you can see, 'V'='W' rule is working only for NVARCHAR column. Can
> anybody explain this behaviour?
Presumably legacy. There are two SQL collations with Swedish in the name:
SQL_SwedishPhone_Pref_CP1_CI_AS and SQL_SwedishStd_Pref_CP1_CI_AS. They
correspond to the two sort orders that shipped with SQL 6.5 I suppose. At
least I recall that there were two sort orders, of which none was agreeable
to us, so we created our own sort order. (We wanted a sort order that was
case-sensitive and used Latin1.)
The difference is that Phone co-sorts V and W and Std does not.
When Unicode was added, the decision was apparently taken to co-sort both
for Unicode. Maybe by oversight. Or maybe because the standard at the time
was to co-sort, and thus the collation was plain wrong.
In any case, I see little reason to use SQL collations. It's better to
use any of the Finnish_Swedish collations. It remains to see if we will get
a set Modern_Swedish collations that sorts V and W separately.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Alex Sadomov
Thank you
> Presumably legacy. There are two SQL collations with Swedish in the
> name: SQL_SwedishPhone_Pref_CP1_CI_AS and
> SQL_SwedishStd_Pref_CP1_CI_AS. They correspond to the two sort orders
> that shipped with SQL 6.5 I suppose. At least I recall that there were
> two sort orders, of which none was agreeable to us, so we created our
> own sort order. (We wanted a sort order that was case-sensitive and
> used Latin1.)
>
> The difference is that Phone co-sorts V and W and Std does not.
>
> When Unicode was added, the decision was apparently taken to co-sort
> both for Unicode. Maybe by oversight. Or maybe because the standard at
> the time was to co-sort, and thus the collation was plain wrong.
>
> In any case, I see little reason to use SQL collations. It's better to
> use any of the Finnish_Swedish collations. It remains to see if we
> will get
> a set Modern_Swedish collations that sorts V and W separately.
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.
> mspx Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>