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)
referenced by