Je, for sure, from Sweden.

by Michael S. Kaplan, published on 2006/06/02 22:33 -04:00, original URI: http://blogs.msdn.com/b/michkap/archive/2006/06/02/615536.aspx


(Apologies for the title; despite the fact that I am not nearly as attractive, there are times I cannot avoid channeling my inner Jamie Lee Curtis!)

Alexey Sadomov asked me (via the contact link):

Hi, Michael.

I've encountered with a problem in sql server related with SQL_SwedishStd_Pref_CP1_CI_AS collation and found your acrticle (
http://blogs.msdn.com/michkap/archive/2006/04/25/583307.aspx).

The problem is that sql server 2000 (and 2005) has different sorting behaviour for VARCHAR and NVARCHAR data:

(Sample truncated, I will show it below -- michael)

As you can see, 'V'='W' rule is working only for NVARCHAR column. Can  you explain this behaviour?

Ok, let's build up the sample first. Run the following code in SQL Server 2000 or SQL Server 2005 (to do it in SQL Server 7.0 you would have to install the server, so we'll avoid that for now; the basic results are the same):

USE master;
IF DB_ID (N'swed_test') IS NOT NULL
    DROP DATABASE swed_test;
CREATE DATABASE swed_test;
GO
use swed_test
CREATE TABLE swed_compat (
    col varchar (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL,
    colN nvarchar (50) COLLATE SQL_SwedishStd_Pref_CP1_CI_AS NULL
)
CREATE TABLE swed_win (
    col varchar (50) COLLATE Finnish_Swedish_CI_AS NULL,
    colN nvarchar (50) COLLATE Finnish_Swedish_CI_AS NULL
)
GO
INSERT INTO swed_compat (col, colN) VALUES ('Val', 'Val')
INSERT INTO swed_compat (col, colN) VALUES ('Wal', 'Wal')
INSERT INTO swed_compat (col, colN) VALUES ('Vel', 'Vel')
INSERT INTO swed_compat (col, colN) VALUES ('Wel', 'Wel')
INSERT INTO swed_win (col, colN) VALUES ('Val', 'Val')
INSERT INTO swed_win (col, colN) VALUES ('Wal', 'Wal')
INSERT INTO swed_win (col, colN) VALUES ('Vel', 'Vel')
INSERT INTO swed_win (col, colN) VALUES ('Wel', 'Wel')

Ok, there is our data, put in two different tables, one with the Windows collation and the other with the SQL compatibility collation. And each table has two columns, one Unicode and one not.

First we will look at the results with the Windows collation (Finnish_Swedish_CI_AS)

SELECT col, colN FROM swed_win ORDER BY col
SELECT col, colN FROM swed_win ORDER BY colN

    Val Val
    Wal Wal
    Vel Vel
    Wel Wel

    Val Val
    Wal Wal
    Vel Vel
    Wel Wel

Look familiar? (cf: It is only of SECONDARY importance)

Ok, now we will look at the SQL Compatibility collation (SQL_SwedishStd_Pref_CP1_CI_AS):

SELECT col, colN FROM swed_compat ORDER BY col
SELECT col, colN FROM swed_compat ORDER BY colN

    Val Val
    Vel Vel
    Wal Wal
    Wel Wel

    Val Val
    Wal Wal
    Vel Vel
    Wel Wel

Uh oh, what happened? Why does sorting by the non-Unicode column break the Swedish/Finnish sort behavior for the SQL compatibility collation?

Or perhaps it never worked properly in prior versions and the bug is that the newer support of Unicode is where the incompatibility is coming from?

Between the two theories, the second seems a bit more likely to me, but I do not have a SQL Server 6.5 handy to install with the older collation to test this theory out; does anyone have one nearby? :-)

In any case, pile this on as another reason that I think SQL Compatibility collations are a bit too retro for me!

 

This post brought to you by(U+ff36, a.k.a. FULLWIDTH LATIN CAPITAL LETTER V)


no comments

referenced by

2007/10/25 Not all in sync quite yet (aka SQL and the CLR and Windows and .NET)

2006/10/29 SQL Server: compatibility collations vs. Window collations

2006/06/06 Unicode and SQL Collations have nothing to do with each other

go to newer or older post, or back to index or month or day