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 😉
-------------------------------------------------------------
"It takes 15 minutes to learn the game and a lifetime to master"
"Share your knowledge. It's a way to achieve immortality."