L' Eomot Inversé (11/1/2012)
Actually, I suspect that the correct way to write the condition, because a collate clause applies only to a single column or constant or variable, is
where CHAR(I) COLLATE Latin1_General_CI_AS
BETWEEN '0' COLLATE Latin1_General_CI_AS
AND 'Z' COLLATE Latin1_General_CI_AS
and I < 256
That would (I hope) ensure that default collations are not used anywhere, so whatever the server default and the database default are the query will always deliver the same answer.
Incorrect. Try the following script and you'll get 62 characters:
CREATE DATABASE qod_collation_db COLLATE Japanese_CI_AS_KS_WS;
GO
USE qod_collation_db;
GO
WITH Numbers AS
( SELECT 0 AS n
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n < 255
)
SELECT n AS code, CHAR(n) AS symbol
FROM Numbers AS n
WHERE CHAR(n) COLLATE Latin1_General_CI_AS BETWEEN '0' COLLATE Latin1_General_CI_AS AND 'Z' COLLATE Latin1_General_CI_AS
OPTION(MAXRECURSION 255);
GO
USE master;
GO
DROP DATABASE qod_collation_db;
GO
What's interesting if we execute 'SELECT CHAR(n)' on a Japanese_CI_AS_KS_WS database we'll get NULLs for the values of 'n' from 129 to 159 and from 224 to 252.
Another example (sorry for using a non-Latin character here; I don't know how you will see that character – in its original form, as a rectangle or something else; actually I'm using a cyrillic letter http://en.wikipedia.org/wiki/Tse_(Cyrillic)):
CREATE DATABASE qod_collation_db COLLATE Cyrillic_General_CI_AS;
GO
USE qod_collation_db;
GO
SELECT
CHAR(246) AS Cyrillic,
CHAR(246) COLLATE Latin1_General_CI_AS AS Latin,
CHAR(246) COLLATE Japanese_CI_AS_KS_WS AS Japanese;
GO
USE master;
GO
DROP DATABASE qod_collation_db;
GO
Here is the result set:
Cyrillic Latin Japanese
-------- ----- --------
? ?
(Note that the 'Japanese' value is a whitespace.)