SQL Server 2008 - different sort orders on VARCHAR

问题:

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?

回答1:

You need to use binary collation to achieve consistent ordering.

ORDER BY t.Name ASC COLLATE Latin1_General_BIN

Edit: Since you can't do the collate in the query, you will need to do it at the database level.

You will need to set it on the column(s) that you are comparing and it needs to be binary.

Here's an example of that.

回答2:

Either change the collation in the database or change the collation of individual columns in the tables you need consistent ordering on.

The collations Latin1_General_BIN or Latin1_General_BIN2 work fine with your example.

回答3:

You can also order the set with CAST(VARCHAR) on a CTE that returns the primary key and do a join on the table to get the NVARCHAR value.