SQL Server 2008 - VARCHAR 与 NVARCHAR 值的不同排序顺序 [英] SQL Server 2008 - different sort orders on VARCHAR vs NVARCHAR values

问题描述

在 SQL Server 2008 中，我在对 NVARCHAR 列进行排序时看到了一些奇怪的行为；这里有几个快速用例来演示:

In SQL Server 2008, I am seeing some strange behavior when ordering NVARCHAR columns; here are a few quick use cases to demonstrate:

案例 1:对 VARCHAR 值进行排序:

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
一个

(有破折号的先显示)

将此与 NVARCHAR 值上的 ORDER 进行对比:

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

产生这个输出:

A
-A

假设我想使用标准的 ORDER BY 子句对 NVARCHAR 字段进行排序(我无法更改数据库设计)(我使用的是 linq2nhib，这会阻止我在此处进行任何转换) - 我如何进行排序以所需的方式工作(首先显示具有前导非字母数字值的项目)?

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?

推荐答案

要么更改数据库中的排序规则，要么更改需要一致排序的表中各个列的排序规则.

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

排序规则 Latin1_General_BIN 或 Latin1_General_BIN2 可以很好地处理您的示例.

The collations Latin1_General_BIN or Latin1_General_BIN2 work fine with your example.

这篇关于SQL Server 2008 - VARCHAR 与 NVARCHAR 值的不同排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！