Fantastic, thanks for going to the trouble of posting this up Chris.

Here's IO and timing stats for both queries as-is:

LIKE ========================================

(229 row(s) affected)

Table 'TestData'. Scan count 9, logical reads 10605, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 765 ms, elapsed time = 142 ms.

PATINDEX =====================================

(229 row(s) affected)

Table 'TestData'. Scan count 1, logical reads 10219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 873 ms, elapsed time = 878 ms.

Here are results with parallelism switched off in the LIKE query

SELECT *

FROM dbo.TestData

WHERE Group3 LIKE '%4E39%'

OPTION (MAXDOP 1);

LIKE ========================================

(229 row(s) affected)

Table 'TestData'. Scan count 1, logical reads 10219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 795 ms, elapsed time = 805 ms.

PATINDEX =====================================

(229 row(s) affected)

Table 'TestData'. Scan count 1, logical reads 10219, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 889 ms, elapsed time = 880 ms.

Because it runs in parallel, the LIKE version is faster in this case.

If you change the queries slightly so they are both parallel and also gobble the output to remove network and display latency, the results are only trivially different:

DECLARE @Datetime DATETIME, @Eater VARCHAR(10)

SET @Datetime = GETDATE()

PRINT 'PATINDEX ====================================='

SELECT @Eater = Group3

FROM dbo.TestData

WHERE PATINDEX('%4E39%',Group3) != 0;

SELECT DATEDIFF(ms,@Datetime,getdate());

-- 146, 146, 163, 146, 146, 146, 156, 153, 140, 150

DECLARE @Datetime DATETIME, @Eater VARCHAR(10)

SET @Datetime = GETDATE()

PRINT 'LIKE ========================================'

SELECT @Eater = Group3

FROM dbo.TestData

WHERE Group3 LIKE '%4E39%'

SELECT DATEDIFF(ms,@Datetime,getdate());

-- 160, 113, 150, 126, 150, 130, 160, 130, 143, 150