hmm.. With (MAXDOP=1)

for SQL 2005

********************************************************************************

Found, no index

********************************************************************************

===== Far Right =====

--------------CharIndex

SQL Server Execution Times:

CPU time = 8640 ms, elapsed time = 5027 ms.

--------------PatIndex %aa%

SQL Server Execution Times:

CPU time = 8750 ms, elapsed time = 4993 ms.

--------------PatIndex %aa

SQL Server Execution Times:

CPU time = 8750 ms, elapsed time = 5034 ms.

--------------Right

SQL Server Execution Times:

CPU time = 703 ms, elapsed time = 427 ms.

--------------Substring

SQL Server Execution Times:

CPU time = 735 ms, elapsed time = 418 ms.

--------------Like %aa%

SQL Server Execution Times:

CPU time = 8594 ms, elapsed time = 4922 ms.

--------------Like %aa

SQL Server Execution Times:

CPU time = 8999 ms, elapsed time = 5404 ms.

================================================================================

===== Far Left =====

--------------CharIndex

SQL Server Execution Times:

CPU time = 1063 ms, elapsed time = 675 ms.

--------------PatIndex %aa%

SQL Server Execution Times:

CPU time = 1109 ms, elapsed time = 724 ms.

--------------PatIndex aa%

SQL Server Execution Times:

CPU time = 8969 ms, elapsed time = 5400 ms.

--------------Left

SQL Server Execution Times:

CPU time = 719 ms, elapsed time = 731 ms.

--------------Substring

SQL Server Execution Times:

CPU time = 719 ms, elapsed time = 735 ms.

--------------Like %aa%

SQL Server Execution Times:

CPU time = 968 ms, elapsed time = 610 ms.

--------------Like aa%

SQL Server Execution Times:

CPU time = 9000 ms, elapsed time = 5226 ms.

================================================================================

===== Middle =====

--------------CharIndex

SQL Server Execution Times:

CPU time = 4610 ms, elapsed time = 2836 ms.

--------------PatIndex %aa%

SQL Server Execution Times:

CPU time = 4874 ms, elapsed time = 2850 ms.

--------------Substring

SQL Server Execution Times:

CPU time = 813 ms, elapsed time = 479 ms.

--------------Like %aa%

SQL Server Execution Times:

CPU time = 4750 ms, elapsed time = 2856 ms.

================================================================================

SQL 2008

********************************************************************************

Found, no index

********************************************************************************

===== Far Right =====

--------------CharIndex

SQL Server Execution Times:

CPU time = 11657 ms, elapsed time = 9175 ms.

--------------PatIndex %aa%

SQL Server Execution Times:

CPU time = 11281 ms, elapsed time = 7517 ms.

--------------PatIndex %aa

SQL Server Execution Times:

CPU time = 10219 ms, elapsed time = 5786 ms.

--------------Right

SQL Server Execution Times:

CPU time = 859 ms, elapsed time = 493 ms.

--------------Substring

SQL Server Execution Times:

CPU time = 718 ms, elapsed time = 438 ms.

--------------Like %aa%

SQL Server Execution Times:

CPU time = 10548 ms, elapsed time = 5706 ms.

--------------Like %aa

SQL Server Execution Times:

CPU time = 10234 ms, elapsed time = 5853 ms.

================================================================================

===== Far Left =====

--------------CharIndex

SQL Server Execution Times:

CPU time = 1109 ms, elapsed time = 711 ms.

--------------PatIndex %aa%

SQL Server Execution Times:

CPU time = 1048 ms, elapsed time = 745 ms.

--------------PatIndex aa%

SQL Server Execution Times:

CPU time = 10577 ms, elapsed time = 5558 ms.

--------------Left

SQL Server Execution Times:

CPU time = 781 ms, elapsed time = 812 ms.

--------------Substring

SQL Server Execution Times:

CPU time = 812 ms, elapsed time = 818 ms.

--------------Like %aa%

SQL Server Execution Times:

CPU time = 1001 ms, elapsed time = 660 ms.

--------------Like aa%

SQL Server Execution Times:

CPU time = 10609 ms, elapsed time = 5585 ms.

================================================================================

===== Middle =====

--------------CharIndex

SQL Server Execution Times:

CPU time = 5454 ms, elapsed time = 3206 ms.

--------------PatIndex %aa%

SQL Server Execution Times:

CPU time = 5874 ms, elapsed time = 3229 ms.

--------------Substring

SQL Server Execution Times:

CPU time = 797 ms, elapsed time = 450 ms.

--------------Like %aa%

SQL Server Execution Times:

CPU time = 5531 ms, elapsed time = 3205 ms.

================================================================================

I have three more machines with sql 05 on them in my house, and tommorow I'll test this code on them too just to see what will happen. I could do it now but it is 2 AM here 😉