是什么让 SQL 语句变得可搜索? [英] What makes a SQL statement sargable?

问题描述

根据定义(至少从我所见)sargable 意味着查询能够让查询引擎优化查询使用的执行计划.我试过查找答案，但似乎没有太多关于这个主题的内容.所以问题是，什么可以或不可以使 SQL 查询成为 sargable?任何文档都将不胜感激.

By definition (at least from what I've seen) sargable means that a query is capable of having the query engine optimize the execution plan that the query uses. I've tried looking up the answers, but there doesn't seem to be a lot on the subject matter. So the question is, what does or doesn't make an SQL query sargable? Any documentation would be greatly appreciated.

供参考:Sargable

推荐答案

使查询不可查询的最常见的事情是在函数中包含一个字段 在 where 子句中:

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:

SELECT ... FROM ...
WHERE Year(myDate) = 2008

SQL 优化器不能在 myDate 上使用索引，即使存在索引.它实际上必须为表格的每一行评估这个函数.更好用:

The SQL optimizer can't use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

其他一些例子:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 

这篇关于是什么让 SQL 语句变得可搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！