Does SQL Server optimize LIKE ('%%') query

问题:

I have a Stored Proc which performs search on records.

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.

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

The Stored proc is like this:

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

回答1:

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

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 requires a table scan, unless there are very few non-null values in the column and it is well indexed.

EDIT

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)

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

回答2:

With regard to an optimal, index-using execution plan - no. The prefixing wildcard prevents an index from being used, resulting in a scan instead.

If you do not have a wildcard on the end of the search term as well, then that scenario can be optimised - something I blogged out a while back: Optimising wildcard prefixed LIKE conditions

Update
To clarify my point:
LIKE 'Something%' - is able to use an index
LIKE '%Something' - is not able to use an index out-of-the-box. But you can optimise this to allow it to use an index by following the "REVERSE technique" I linked to.
LIKE '%Something%' - is not able to use an index. Nothing you can do to optimise for LIKE.

回答3:

If you use a LIKE clausule, and specify a wildcard-character (%) as a prefix of the searchstring, SQL Server (and all other DBMS'es I guess) will not be able to use indexes that might exists on that column.

I don't know if it optimizes the query if you use an empty search-argument ... Perhaps your question may be answered if you look at the execution plan ?

Edit: I've just checked this out, and the execution plan of this statement:

select * from mytable

is exactly the same as this the exec plan of this statement:

select * from mytable where description like '%'

Both SQL statements simply use a clustered index scan.