views:

71

answers:

0

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?