SQL Server:整理 CS->CI 性能与 UPPER [英] SQL Server: COLLATE CS->CI performance vs UPPER

问题描述

在比较可能包含不同大小写值的列时，使用什么更好?校对速度更快吗?只是想弄清楚这一点，因为在我们的项目中有大量数据比较.我们使用:

What's better to use when comparing columns that could contains values with different cases? Is Collate faster? Just trying to figure this out because in our project with tons of data comparisons. we use:

select * from table t1, table t2 where 
t1.col1 collate SQL_Latin1_General_CP1_CI_AS = t2.colb collate SQL_Latin1_General_CP1_CI_AS 

为什么我们不能重写为:

why we can't rewrite as:

select * from table t1, table t2 where 
UPPER(t1.col1)  = UPPER(t2.colb)

推荐答案

你试过了吗?

我建议它们同样糟糕，因为由于函数/COLLATE 无法使用索引.与扫描大表相比，COLLATE 或 UPPER 之一的开销很小.

I'd suggest they'd be equally bad because an index can't be used because of the function/COLLATE. The overhead of one of COLLATE or UPPER will be tiny compared to scanning a large table.

我个人会使用 UPPER，因为它更明显.使用 UPPER 还会告诉我列是区分大小写的.

I would use UPPER personally because it's more obvious. The use of UPPER would also tell me the columns are case sensitive anyway.

编辑，2012 年 10 月

Edit, Oct 2012

UPPER 的优势在于与排序规则无关.

UPPER has the advantage of being collation independent.

基本上，使用 COLLATE 需要强制代码页.UPPER 不会.因此，您可以保留预期的排序/比较逻辑，而不是强制使用标准拉丁语.

Using COLLATE requires to force a code page, basically. UPPER won't. So you can keep your intended sort/compare logic rather than forcing standard Latin.

这篇关于SQL Server:整理 CS->CI 性能与 UPPER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！