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

Question

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

Answer 1

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.