Hi there,
I really have to say I did not think my article will have a response like this but this make me feel good and I know that it all did not go into the wind. My main purpose of this article was for people that are new at SQL to at least have something to help them. Jeff, I did your test as well and I will definitely keep the code for future reference. Here is my results and although it does not look as well as yours, it looks better than the others that I have seen.
(1000000 row(s) affected)
********************************************************************************
Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2092 ms, elapsed time = 1050 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2626 ms, elapsed time = 1334 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 2641 ms, elapsed time = 1373 ms.
--------------Right
SQL Server Execution Times:
CPU time = 640 ms, elapsed time = 318 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 329 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 2031 ms, elapsed time = 1016 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 2656 ms, elapsed time = 1428 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 478 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 672 ms, elapsed time = 359 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 2906 ms, elapsed time = 1785 ms.
--------------Left
SQL Server Execution Times:
CPU time = 469 ms, elapsed time = 461 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 468 ms, elapsed time = 464 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 626 ms, elapsed time = 318 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 2937 ms, elapsed time = 1465 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1375 ms, elapsed time = 693 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1640 ms, elapsed time = 824 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 688 ms, elapsed time = 367 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1313 ms, elapsed time = 666 ms.
================================================================================
********************************************************************************
NOT Found, no index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1625 ms, elapsed time = 822 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2156 ms, elapsed time = 1078 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 2188 ms, elapsed time = 1089 ms.
--------------Right
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 122 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 129 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1624 ms, elapsed time = 808 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 2188 ms, elapsed time = 1087 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1641 ms, elapsed time = 839 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2125 ms, elapsed time = 1075 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 2156 ms, elapsed time = 1090 ms.
--------------Left
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 128 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 132 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1640 ms, elapsed time = 817 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 2188 ms, elapsed time = 1097 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1656 ms, elapsed time = 822 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2125 ms, elapsed time = 1073 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 130 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1593 ms, elapsed time = 800 ms.
================================================================================
********************************************************************************
Found, WITH index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 2062 ms, elapsed time = 1059 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2642 ms, elapsed time = 1338 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 2610 ms, elapsed time = 1375 ms.
--------------Right
SQL Server Execution Times:
CPU time = 624 ms, elapsed time = 322 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 641 ms, elapsed time = 369 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1952 ms, elapsed time = 1076 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 2657 ms, elapsed time = 1354 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 703 ms, elapsed time = 396 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 719 ms, elapsed time = 352 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 2938 ms, elapsed time = 1478 ms.
--------------Left
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 458 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 453 ms, elapsed time = 458 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 332 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 2937 ms, elapsed time = 1460 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1360 ms, elapsed time = 685 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 1656 ms, elapsed time = 828 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 656 ms, elapsed time = 334 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1328 ms, elapsed time = 669 ms.
================================================================================
********************************************************************************
NOT Found, With index
********************************************************************************
===== Far Right =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1625 ms, elapsed time = 810 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2219 ms, elapsed time = 1118 ms.
--------------PatIndex %aa
SQL Server Execution Times:
CPU time = 2219 ms, elapsed time = 1103 ms.
--------------Right
SQL Server Execution Times:
CPU time = 219 ms, elapsed time = 115 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 120 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1593 ms, elapsed time = 793 ms.
--------------Like %aa
SQL Server Execution Times:
CPU time = 2141 ms, elapsed time = 1077 ms.
================================================================================
===== Far Left =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1593 ms, elapsed time = 809 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2157 ms, elapsed time = 1081 ms.
--------------PatIndex aa%
SQL Server Execution Times:
CPU time = 2187 ms, elapsed time = 1092 ms.
--------------Left
SQL Server Execution Times:
CPU time = 220 ms, elapsed time = 120 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 218 ms, elapsed time = 121 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1594 ms, elapsed time = 797 ms.
--------------Like aa%
SQL Server Execution Times:
CPU time = 2140 ms, elapsed time = 1078 ms.
================================================================================
===== Middle =====
--------------CharIndex
SQL Server Execution Times:
CPU time = 1594 ms, elapsed time = 809 ms.
--------------PatIndex %aa%
SQL Server Execution Times:
CPU time = 2188 ms, elapsed time = 1083 ms.
--------------Substring
SQL Server Execution Times:
CPU time = 250 ms, elapsed time = 123 ms.
--------------Like %aa%
SQL Server Execution Times:
CPU time = 1609 ms, elapsed time = 818 ms.
================================================================================
Thanks for all the input to this article guys 'n gals!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)