SQL Server 是否优化 LIKE ('%%') 查询? [英] Does SQL Server optimize LIKE ('%%') query?

问题描述

我有一个对记录执行搜索的存储过程.

I have a Stored Proc which performs search on records.

问题是一些来自 UI 的搜索条件可能是空字符串.因此，当未指定条件时，LIKE 语句变得多余.

The problem is that some of the search criteria,which are coming from UI, may be empty string. So, when the criteria not specified, the LIKE statement becomes redundant.

如何有效地执行该搜索或 Sql Server?或者，它是否优化了 LIKE('%%') 查询，因为它意味着没有什么可比较的?

How can I effectively perform that search or Sql Server? Or, Does it optimize LIKE('%%') query since it means there is nothing to compare?

存储过程是这样的:

ALTER PROC [FRA].[MCC_SEARCH]
@MCC_Code varchar(4),
@MCC_Desc nvarchar(50),
@Detail nvarchar(50)
AS
BEGIN             
       SELECT
             MCC_Code,
             MCC_Desc,
             CreateDate,
             CreatingUser

       FROM
              FRA.MCC (NOLOCK)
       WHERE
             MCC_Code LIKE ('%' + @MCC_Code + '%')
             AND MCC_Desc LIKE ('%' + @MCC_Desc + '%')
             AND Detail LIKE ('%' + @Detail + '%')
       ORDER BY MCC_Code

END

推荐答案

简短的回答是 - 否长答案是 - 绝对不会

The short answer is - no The long answer is - absolutely not

它是否优化了 LIKE('%%') 查询，因为它意味着没有什么可比较的?

Does it optimize LIKE('%%') query since it means there is nothing to compare?

该陈述不真实，因为有可以比较的东西.以下是等价的

The statement is untrue, because there is something to compare. The following are equivalent

WHERE column LIKE '%%'
WHERE column IS NOT NULL

IS NOT NULL 需要进行表扫描，除非该列中的非空值很少并且索引良好.

IS NOT NULL requires a table scan, unless there are very few non-null values in the column and it is well indexed.

有关 SQL Server 中动态搜索过程的资源:
您只需必须阅读 SQL Server MVP Erland Sommarskog 的这篇文章http://www.sommarskog.se/dyn-search.html(选择您的版本，或同时阅读)

Resource on Dynamic Search procedures in SQL Server:
You simply must read this article by Erland Sommarskog, SQL Server MVP http://www.sommarskog.se/dyn-search.html (pick your version, or read both)

否则，如果您需要 CONTAINS 样式搜索的良好性能，请考虑使用 SQL Server 全文引擎.

Otherwise if you need good performance on CONTAINS style searches, consider using SQL Server Fulltext engine.

这篇关于SQL Server 是否优化 LIKE ('%%') 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！