Is PATINDEX faster than LIKE? | TSQL

original post: Is PATINDEX faster than LIKE?
Originally posted here.

I keep seeing the same suggestion on various "tips and tricks" websites: For
situations in which you might want to use LIKE in the WHERE clause, but for
which indexes cannot be used, PATINDEX will perform faster.

So, according to these sources, this:

SELECT *
FROM tbl
WHERE PATINDEX('%abc%', col) > 0

is faster than this:

SELECT *
FROM tbl
WHERE col LIKE '%abc%'

Anyway, here's what I did today... First, I created a big table of test data
(83 million rows) with the following (which you may notice that I lifted from a
previous post):

SELECT DISTINCT A.Name + B.Name + C.Name AS SomewhatLargeString
INTO #BigTableOfStrings
FROM master..spt_values A,
 master..spt_values B,
 master..spt_values C
WHERE a.TYPE NOT IN ('P', 'R', 'F', 'F_U')
 AND b.TYPE NOT IN  ('P', 'R', 'F', 'F_U')


CREATE CLUSTERED INDEX CI_LargeString ON #BigTableOfStrings(SomewhatLargeString)

I decided to test against the pattern '%ossDbOwnChainRefere%', for which
there are 1752 rows in the test table.

First, I ran the LIKE query:

SELECT COUNT(*)
FROM #BigTableOfStrings
WHERE SomewhatLargeString LIKE '%ossDbOwnChainRefere%'

Runtime: 9:55.

Then I tried PATINDEX:

SELECT COUNT(*)
FROM #BigTableOfStrings
WHERE PATINDEX('%ossDbOwnChainRefere%', SomewhatLargeString) > 0

Runtime: 9:56 (yes, worse)