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

问题描述

根据定义(至少从我所见)来看，sargable 意味着查询能够让查询引擎优化查询使用的执行计划.我已经尝试查找答案，但主题似乎并不多.所以问题是，什么使 SQL 查询可搜索或不可用?任何文档将不胜感激.

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屋！