为什么不显式COLLATE覆盖数据库排序规则？ [英] Why doesn't explicit COLLATE override database collation?

问题描述

我在SQL Server 2008 R2开发人员上，服务器默认排序规则为Cyrillic_General_CI_AS

I am on SQL Server 2008 R2 dev, server default collation is Cyrillic_General_CI_AS

在SSMS中执行

选择'éÉâààÀëËçæà'COLLATE Latin1_General_CS_AS

或

Executing in SSMS
SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CS_AS
or

SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CI_AI   

输出

• eEaAaAeEc？a on（使用/使用dbName）默认排序规则Cyrillic_General_CI_AS


• éÉââàÀëËçæà的数据库上默认排序规则Latin1_General_CI_AS

推荐答案

首先将查询中的那些字符文字在数据库设置的任何排序规则下转换为varchar字符串，然后在< a href = http://msdn.microsoft.com/en-us/library/ms184391.aspx rel = noreferrer>归类强制转换生效。

Those character literals in your queries are first converted to varchar strings under whatever collation the database is set for, and then your collation cast takes effect.

如果要传递这样的字符文字并确保忠实地代表所有字符，则最好将它们作为nvarchar文字传递：

If you want to pass such character literals and ensure all characters are faithfully represented, it's better to pass them as nvarchar literals:

create database CollTest collate Cyrillic_General_CI_AS
go
use CollTest
go
SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CS_AS   
SELECT 'éÉâÂàÀëËçæà' COLLATE Latin1_General_CI_AI
go
SELECT N'éÉâÂàÀëËçæà' COLLATE Latin1_General_CS_AS   
SELECT N'éÉâÂàÀëËçæà' COLLATE Latin1_General_CI_AI
go

输出：

-----------
eEaAaAeEc?a

(1 row(s) affected)


-----------
eEaAaAeEc?a

(1 row(s) affected)


-----------
éÉâÂàÀëËçæà

(1 row(s) affected)


-----------
éÉâÂàÀëËçæà

(1 row(s) affected)

这篇关于为什么不显式COLLATE覆盖数据库排序规则？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！