In SQL Server 2008, I am seeing some strange behavior when ordering NVARCHAR columns; here are a few quick use cases to demonstrate:
Case 1: ORDER on VARCHAR values:
SELECT t.Name
FROM
(
SELECT CAST('A' AS VARCHAR(500)) As Name
UNION SELECT CAST('-A' AS VARCHAR(500)) AS NAME
) As t
ORDER BY t.Name ASC
Which produces (my desired) output of:
-A
A
(The one with the leading dash is displayed first)
Contrast this with the ORDER on NVARCHAR values:
SELECT t.Name
FROM
(
SELECT CAST('A' AS NVARCHAR(500)) As Name
UNION SELECT CAST('-A' AS NVARCHAR(500)) AS NAME
) As t
ORDER BY t.Name ASC
Which produces this output:
A
-A
Assuming I want to sort on NVARCHAR fields (I can't change the db design) using a standard ORDER BY clause (I'm using linq2nhib, which prevents me from doing any casting here) - how do I get the sorting to work in the desired fashion (item with the leading non-alphanumeric value displays first)?
I'm hoping there is some sort of database/server-level collation setting for this...any ideas?