LIKE vs PATINDEX

We had a discussion on some of the performance warnings in VSTF like using LIKE(with %) operator. I was just going through multiple options on how to avoid LIKE clause from the query and
improve performance as well. Generally when we use LIKE operator, it goes for table/clustered index scan and this is the costliest operation SQL server performs so we need to be very carefull here.

 First option was to use some other wild card characters instead of (%)  but that does not work here as we search specific words/sentence. If a single character is unknow, then we can use range else % is the only one left to us. Further to avoid this, thought of using any other function, which would avoid using wild card characters and improve performance as well.

 Tried using CHARINDEX & PATINDEX, which can substitute the like operators and tested the same, which seems to be better than LIKE.

 Here is the example.

 Using LIKE Operator:

DBCC DROPCLEANBUFFERS

GO

DBCC FREEPROCCACHE

GO

SET STATISTICS IO ON;

SELECT *

FROM tblContractSchedule 

WHERE ScheduleName LIKE '%INVALID%'

AND ScheduleName LIKE '%DO NOT USE%'

 STATS IO ON Result:

Table 'tblContractSchedule'. Scan count 5, logical reads 9287, physical reads 13, read-ahead reads 8423, lob logical reads 0,lob physical reads 0, lob read-ahead reads 0.

Execution Plan :

 Using PATINDEX:

DBCC DROPCLEANBUFFERS

GO

DBCC FREEPROCCACHE

GO

SET STATISTICS IO ON;

SELECT *

FROM tblContractSchedule 

WHERE PATINDEX('%INVALID%',ScheduleName) > 0

AND PATINDEX('%DO NOT USE%',ScheduleName) > 0

Table 'tblContractSchedule'. Scan count 1, logical reads 8406, physical reads 9,read-ahead reads 8423, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan :

As per this test, it seems that we can straight away get rid of LIKE operator and reduce our warnings and improve the query performance.