adam machanic now blogs at http://sqlblog.com

Is PATINDEX faster than LIKE?

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%'

The thing is, I'm not one to just take this kind of stuff at face value, so I've tested this assertion several times. Every time I see this tip, I think, "I must be missing something," and I test again. And every single time I test again, with different data, different patterns, etc, I arrive at the same conclusion: They perform exactly the same.

Which brings us to today. In the SQL Server Central forums, William O'Malley told me that in his tests, on his data that he can't post due to his industry (?), PATINDEX does outperform LIKE.

So I decided to test yet again. And I'm still coming up with the exact same numbers. I'm hoping that some reader will be able to tell me this mysterious circumstance in which PATINDEX really does outperform LIKE. Or maybe explain why my test is totally incorrect.

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)

Then I ran LIKE again with a runtime of 9:47, then PATINDEX again with a runtime of 9:50, and now I'm not patient enough to run either of them again.

So am I correct? Is this claim bogus? Or have I gone completely off-base?