• mtassin (11/1/2012)


    What's interesting for me is that the query from the original question returns 120 rows.

    select CHAR(I),I from Tally

    where char(I) between '0' and 'Z' collate latin1_general_ci_as

    and I < 256 order by CHAR(I)

    However, your query gives 139. I also realize why here (one's apply the collation to 0 thru Z the other to the result of the CHAR function. But I did find that interesting.

    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.

    It was careless of me to use a query that doesn't correctly eliminate default collations in my explanation.

    Tom