在where子句SARGable上的串联字符串是否可用? [英] Is concatenated string on where clause SARGable?

问题描述

假设我在两个nvarchar列A和B上有一个非聚集索引.

Let's say I have a nonclustered index on two nvarchar columns, A and B.

如果我的查询看起来像这样:

If my query looks something like this:

SELECT Columns FROM Table WHERE A + B = '1234'

查询可以有效使用索引吗?

Can the query effectively use the index?

或者我应该在where子句中分隔列

Or should I separate the columns in where clause

SELECT Columns FROM Table WHERE A = '12' AND B = '34'

我从测试中发现了非常令人惊讶的结果.两者都产生了相同的查询计划，但是成本不同.在大多数情况下，级联查询会更快，但有时分离版本会更快.

I've found pretty surprising results from my testings. Both produced an identical query plan, but the costs were different. Most of the time, the concatenated query would be faster but from time to time, the separated version would be faster.

推荐答案

应用于分节符 SARG 的任何表达式，函数，计算.主要公式如下:

Any expression, function, calculation applied to column breaks SARGability. The main formula looks like:

列 运算符 value 或

值 运算符 列.

列应该只是列名.运算符可以在 =，>，< =，> =之间，例如.值可以是常数，也可以是任何表达式. Like 应该像 like'AAA％_ .如果 Like 是％AAA 或 _AAA ，则不能 SARG .

Column should be just column name. Operator can be =, >, <=, >=, between, like. Value can be constant or any expression. Like should be like like 'AAA%_. If Like is %AAA or _AAA it is not SARGable.

因此答案是:如果您可以将谓词拆分为 WHERE A ='12'AND B ='34'，则将使用索引(如果存在). WHERE A + B ='1234'不会使用索引.

So the answer is: if you can split your predicate to WHERE A = '12' AND B = '34', this will use index if any appropriate exists. This WHERE A + B = '1234' won't use index.

这篇关于在where子句SARGable上的串联字符串是否可用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！