Weird SQL Server 2005 Collation difference between varchar() and nvarchar()
- by richardtallent
Can someone please explain this:
SELECT
CASE WHEN CAST('iX' AS nvarchar(20))
> CAST('-X' AS nvarchar(20)) THEN 1 ELSE 0 END,
CASE WHEN CAST('iX' AS varchar(20))
> CAST('-X' AS varchar(20)) THEN 1 ELSE 0 END
Results: 0 1
SELECT
CASE WHEN CAST('i' AS nvarchar(20))
> CAST('-' AS nvarchar(20)) THEN 1 ELSE 0 END,
CASE WHEN CAST('i' AS varchar(20))
> CAST('-' AS varchar(20)) THEN 1 ELSE 0 END
Results: 1 1
On the first query, the nvarchar() result is not what I'm expecting, and yet removing the X make the nvarchar() sort happen as expected.
(My original queries used the '' and N'' literal syntax to distinguish varchar() and nvarchar() rather than CAST() and got the same result.)
Collation setting for the database is SQL_Latin1_General_CP1_CI_AS.
